Comparison of SQL Server three import Export data methods

When we build a database and want to aggregate the different types of databases scattered across the database in this new database, especially when it comes to data validation, decontamination, and transformation, there are big challenges. Fortunately, SQL Server provides us with powerful, rich data import and export capabilities, and when importing exports, we can flexibly handle the data.

There are three main ways to import Export data in SQL Server: Use Transact-SQL to process data, call command-line tools bcp to process data, and use Data Transformation Services (DTS) to process data. These three methods each have their own characteristics, the following are the main characteristics of their comparison.

A comparison of the way of use

1. Using Transact-SQL for data import and export

It is easy to see that a Transact-SQL approach is a way of importing or aggregating data from the same or different types of databases into one another in a way that can be done by means of statements. If you are importing data into and out of different SQL Server databases, it is very easy to do so. You can generally use SELECT INTO and insert INTO. The table into which you use SELECT into is required to exist, meaning that its function is to create an empty table before the data is directed, and then import the data from the source table into the newly created empty table, which is equivalent to copying the table (and not replicating the table's index, etc.). The function of INSERT into is to insert the source data into a table that already exists, you can use it for data merging, and you can use update if you want to update a record that already exists.

SELECT * INTO table2 FROM table1 --table1和table2的表结构相同
INSERT INTO table2 SELECT * FROM table3 --table2和table3的表结构相同

The situation becomes much more complex when data is imported and exported between heterogeneous databases. The first thing to solve is how to open a non-SQL Server database.

Two functions are available in SQL Server to open and manipulate these databases based on the OLE DB provider of various types of databases, both OPENDATASOURCE and OpenRowset. Their functions are basically the same, the difference is mainly two points.

(1) different ways of calling.

There are two parameters for OpenDataSource, namely OLE DB provider and connection strings. Using OpenDataSource is only the equivalent of referencing a database or a service (for databases such as SQL Server, Oracle, and so on). To refer to a datasheet or view in it, you must have the OpenDataSource (...) After the reference.

In SQL Server, query the Table1 table in the Access database Abc.mdb by OpenDataSource:

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Provider=Microsoft.Jet.OLEDB.4.0;
Data Source=abc.mdb;Persist Security
Info=False')...
table1

OPENROWSET is equivalent to a recordset that can be used directly as a table or view.

In SQL Server, query the Table1 table in the Access database Abc.mdb by Openrowsete:

SELECT * FROM OPENROWSET
('Microsoft.Jet.OLEDB.4.0', 'abc.mdb';
'admin';'','SELECT * FROM table1')

(2) flexibility of different.

OpenDataSource can only open tables or views in the appropriate database, and can only be processed in SQL Server if filtering is required. OPENROWSET can filter the database while it is open, as in the example above, in OPENROWSET you can use the SELECT * from Table1 queries the datasheet in Abc.mdb, and OPENDATASOURCE can only reference table1, and cannot query table1. Therefore, OPENROWSET is more flexible than opendatasource.