SQL Server data import and export technology overview and Comparison

SQL Server data import and export technology overview and Comparison

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 structure of Table1 and Table2 is the same
Insert into Table2 select * From table3 -- table 2 and table 3 have the same structure.
 

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.

2. Use the command line bcp to import and export data

Many large systems not only provide user-friendly graphical user interfaces, but also provide command line control for the system. In addition to using SQL statements to operate data in SQL Server, you can also use the command line tool bcp to perform the same operations on data.

BCP is a tool based on the database-library client library. It is very powerful. BCP can copy data from multiple clients to a single table in parallel, greatly improving the loading efficiency. However, when performing parallel operations, you must note that only applications that use APIs Based on ODBC or SQL OLE DB are used.ProgramTo load data to a single table in parallel.

BCP can export data from SQL Server to any database supported by OLE DB. The following statement exports the authors table to an Excel file:

BCP pubs. DBO. Authors out C:/temp1.xls-C-Q-s "gnetdata/gnetdata"-U "sa"-P "password"
 

BCP can be executed not only through command lines, but also through SQL. This requires a system stored procedure xp_mongoshell. The preceding command can be rewritten as follows.

Exec master .. xp_mongoshell 'bcp pubs. DBO. Authors out
C:/temp1.xls-C-Q-s "gnetdata/gnetdata"-U "sa"-P "password "'
 

3. Use DTS to import and export data

DTS is the core of data import and export in SQL Server. In addition to its functions related to SQL and command line tool BCP, you can also use VBscript, JScript, and other script languages to test, purify, and convert data.

SQL Server provides graphical user interfaces for DTS. You can use the graphical interface to import and export data and process the data accordingly. At the same time, DTS also provides programming interfaces in the form of COM components. That is to say, any development tool supporting COM components can use the functions provided by DTs by COM components. Data transmission can be saved in different forms in SQL Server. It can be a package or a visual basic source program file, in this way, you only need to compile the data transmission COM component in VB.

The biggest difference between DTs and other data import and export methods is that DTS can process each row of data in depth during data processing. Below is a piece of VBScriptCodeThis code is executed when DTS processes each record. dtsdestination indicates the target record, dtssource indicates the source record, and while processing the marital status, convert 0 or 1 of the Marital Status in the source record to "married" or "Unmarried" in the target record ".

Function main ()
Dtsdestination ("name") = dtssource ("name ")
Dtsdestination ("Age") = dtssource ("Age ")
If dtsdestination ("Marital status") = 1 then
Dtsdestination ("Marital status") = "married"
Else
Dtsdestination ("Marital status") = "Unmarried"
End if
Main = dtstransformstat_ OK
End Function
 

The three data import and export methods have their own advantages and disadvantages. The comparison between them is shown in 1.

2. Performance Comparison

Use the transact-SQL method. The Import and Export speed between SQL Server databases is very fast, but using the OpenDataSource and OpenRowset methods to open and operate the database using OLE DB Provider slows down the speed.

Use the BCP command. If you do not need to perform data verification or other operations, you can use it very quickly because it uses the DB-library of the C interface internally, therefore, the database operation speed is greatly improved.

Using DTS to import data should be the best method. Because it integrates Microsoft Universal Data Access Technology and Microsoft ActiveX technology, it can not only flexibly process data, but also very efficient in data import and export.

Summary

SQL Server provides a variety of data import and export methods, which gives us more options, but this will bring us a new problem: how can I select an appropriate data import and export method based on the actual situation? I will provide some personal suggestions here, hoping to provide some guidance for readers.

If you want to import and export data between SQL Server databases and do not need to perform complex checks on the data, it is best to use the transact-SQL method for processing, because SQL is very fast when performing data operations between SQL Server databases. Of course, if you want to perform complex operations, such as data verification and conversion operations, it is best to use DTS for processing, because DTS is not light-Driven Data efficiency, in addition, it can deeply control data. However, the data transmission programming interface is based on COM, and this interface is very complex. Therefore, it will become complicated to call data transmission using a program. Therefore, when the data volume is not large, you can use OpenDataSource or OpenRowSet to add the data import and export function to a program without complicated data processing functions.

The BCP command is not suitable for calling through a program. If you need to import data in batches, you can call the BCP command through the batch processing file. In this way, you do not need to write a large number of programs, you do not need to switch between different operation interfaces in the Enterprise Manager to import and export data. Therefore, it is suitable for quick data import and export when the client is not installed with the Enterprise Manager or when SQL Server Express is used.