Title: use commands to import and export ms SQL data

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, will Face
Big 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 data conversion Service (DTS) to process 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. If it is in a different
It is very easy to import and export data between SQL Server databases. Select into from and insert into are generally used. When select into from is used, the table after INTO is required.
It must exist, that is, its function is to create an empty table before importing data from the source table to the new empty table, this is equivalent to table replication (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. They
The 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, etc.
Database ). 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 be used to open a database
Filter. In the preceding example, you can use SELECT * FROM table1 in OPENROWSET to query data tables in abc. mdb. OPENDATASOURCE can only reference table1 but cannot query data tables.
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 SQL Server, you can use SQL statements to operate data.
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, and BCP can concurrently extract data from multiple customers
Large data volumes are copied to a single table, 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 can execute
Operations for loading 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 check data flexibly.
Verification, purification, and conversion.
SQL Server provides graphical user interfaces for DTS. You can use the graphical interface to import and export data and process the data accordingly. DTS also provides programming interfaces in the form of com components,
That is to say, any development tool that supports 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 in the form of a package or saved
Visual Basic source program file, so that you can use the DTS com component as long as it is compiled 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. The following is a piece of VBScript code.
When a record is executed, DTSDestination indicates the target record, and DTSSource indicates the source record. When "Marital Status" is handled, convert 0 or 1 of the Marital Status in the source record to
Or "Unmarried ".
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.
2. Performance Comparison
Use the Transact-SQL method. If it is an SQL Server database, the import and export speed will be very fast, but the use of OPENDATASOURCE and OPENROWSET Method Using OLE DB Provider
The speed of enabling and operating databases will be slower.
Use the BCP command. If you do not need to verify the data, you can use it very quickly. This is because it uses the DB-library of the c interface internally, so the speed of Database Operations
Great improvements have been made.
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
Data, and the efficiency of data import and export is very high.
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 to select appropriate data import and export according to the actual situation
What about the outbound method? 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 in SQL Server Data
When performing data operations between databases, SQL is very fast. Of course, if you want to perform complex operations, such as data validation and conversion operations, you 'd better use DTS for processing, because DTS does not guide data efficiency.
High, and the ability to deeply control data. However, the DTS Programming Interface is based on com, and this interface is very complex. Therefore, it will become complicated to call DTS using a program. Therefore, when the data
You can use OPENDATASOURCE or OPENROWSET for processing when you want to add the data import and export function to a program without complex 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
In the business manager, data is imported and exported through switching between different operation interfaces. 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.
. -

Article Source: http://www.diybl.com/course/7_databases/ SQL /sqlServer/2008626/128402.html