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

Source: Internet
Author: User
Tags sql server express

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 using APIs Based on ODBC or SQL OLE DB can 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. The following is a piece of VBScript code. This code is executed when DTS processes each record. dtsdestination indicates the target record, and dtssource indicates the source record. when 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.

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.