Overview and comparison of SQL Server data import and export technologies

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.

Querying table1 tables in an Access database Abc.mdb in SQL Server 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.

Querying table1 tables in an Access database Abc.mdb in SQL Server 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.

2. Import Export data using command line bcp

Many large systems provide not only a friendly graphical user interface, but also a command-line approach to control the system. In addition to using SQL statements to manipulate data in SQL Server, you can use a command-line tool to do the same thing with BCP.

BCP is a tool based on the Db-library client library. It is so powerful that BCP can significantly increase the load efficiency by replicating data from multiple clients to a single table in a parallel manner. However, when performing parallel operations, it should be noted that only applications that use an API based on ODBC or SQL OLE DB can perform operations that load data into a single table in parallel.

BCP can export data from SQL Server to a database supported by any OLE DB, as 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 the command line, but also through SQL, which requires a system stored procedure xp_cmdshell to be implemented, as the above command can be rewritten as follows.

EXEC master..xp_cmdshell 'bcp pubs.dbo.authors out c:\temp1.xls -c -q -S"GNETDATA/GNETDATA" -U"sa" -P"password"'

3. Import Export data Using Data Transformation Services (DTS)

DTS, which is the core of importing exported data in SQL Server, provides the flexibility to verify, purify, and transform data in scripting languages such as VBScript, JScript, and so on, in addition to the functionality that is appropriate for SQL and command-line tools bcp.

SQL Server provides a graphical user interface for DTS, where users can import and export data using a graphical interface and handle the data accordingly. At the same time, DTS also provides programming interfaces in the form of COM components, meaning that any development tool that supports COM components can take advantage of the functionality provided by DTS using the COM component. DTS can be saved in a different form in SQL Server, either in the form of a package or as a Visual Basic source program, so that DTS COM components can be used as long as you compile in VB.

The biggest difference between DTS and other data import methods is that it can be processed in depth for each row of data in the process of processing data. Here is a snippet of VBScript code, which executes at the time of each record in DTS, dtsdestination the target record, DTSSource represents the source record, and, when dealing with "marital status," converts 0 or 1 of the "marital status" in the source record to the target record. "Married "or" unmarried ".

Function Main()
DTSDestination("姓名") = DTSSource("姓名")
DTSDestination("年龄") = DTSSource("年龄")
If DTSDestination("婚姻状况") = 1 Then
DTSDestination("婚姻状况") = "已婚"
Else
DTSDestination("婚姻状况") = "未婚"
End If
Main = DTSTransformStat_OK
End Function

The above three kinds of data import and export methods have their advantages and disadvantages, they are compared to each other as shown in Figure 1.