Comparison of data import and export methods for SQL Server databases (1)

When we create a database and want to classify different types of databases scattered in different places in this new database, especially during data validation, purification, and conversion, there will be great challenges. Fortunately, SQL Server provides us with powerful and rich data import and export functions, and supports flexible data processing while importing and exporting data.

 

There are three main ways to import and export data in SQL Server: Using Transact-SQL to process data; calling the command line tool bcp to process data; using the data conversion Service (DTS) process the data. These three methods have their own characteristics. The following describes their main features.

 

I. Usage comparison

 

1. Use Transact-SQL to import and export data

 

We can easily see that the transact-SQL method is to import, export, or aggregate data from the same or different types of databases in one place through SQL statements. It is easy to import and export data between different SQL Server databases. Select into from and insert into are generally used. When select into from is used, the table followed by into must exist. That is to say, its function is to create an empty table before importing data, then, import the data in the source table to the new empty table, which is equivalent to copying the table (the table index and other information will not be copied ). The insert into function inserts the source data into an existing table and can be used for data merging. to update an existing record, you can use update.

 

Select * into Table2 from Table1

 

-- The table structures of Table1 and Table2 are the same.

 

Insert into Table2 select * From table3

 

-- The table structures of Table2 and table3 are the same.

 

It is much more complicated to import and export data between heterogeneous databases. The first thing to solve is how to open a non-SQL Server database.

 

Two functions are provided in SQL Server to open and operate these databases based on OLE DB providers of various types of databases. These two functions are OpenDataSource and OpenRowset. Their functions are basically the same, and there are two main differences.

 

(1) The call method is different.

 

OpenDataSource has two parameters: ole db provider and connection string. Using OpenDataSource is only equivalent to referencing databases or services (for SQL Server, Oracle, and other databases ). To reference a data table or view, you must reference it after OpenDataSource.

 

Use OpenDataSource in SQL Server to query table 1 in Access Database ABC. MDB

 

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 record set and can be directly used as a table or view.

 

Use openrowsete in SQL Server to query table 1 in Access Database ABC. MDB

 

Select * From OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'abc. mdb ';

 

'Admin'; '', 'select * From table1 ')

 

(2) flexibility is different.

 

OpenDataSource can only open tables or views in the corresponding database. If filtering is required, it can only be processed in SQL Server. OpenRowSet can filter the database when it is opened. In the preceding example, select * From Table1 to ABC can be used in OpenRowSet. while OpenDataSource can only reference Table1, but cannot query table1. Therefore, OpenRowSet is more flexible than OpenDataSource.