When we build a database and want to classify and summarize different types of databases scattered everywhere in this new database, especially when performing data inspection, purification and transformation, we will Faced with great challenges. Fortunately, SQL Server provides us with powerful and rich data import and export functions, and can flexibly process data while importing and exporting.

There are three main ways to import and export data in SQL Server: use Transact-SQL to process data; call the command line tool BCP to process data; use Data Transformation Service (DTS) to process data. Each of these three methods has its own characteristics. Their main characteristics are compared below.

1. Comparison of usage methods

1. Use Transact-SQL for data import and export

We can easily see that the Transact-SQL method uses SQL Statement method is a method of importing and exporting data from the same or different types of databases or bringing them together in one place. If you are importing and exporting data between different SQL Server databases, it will be very easy to do. Generally, SELECT INTO FROM and INSERT INTO can be used. When using SELECT INTO FROM, the table followed by INTO must exist. That is to say, its function is to create an empty table before importing data, and then import the data in the source table into the newly created empty table. This is equivalent to the table Copying (information such as table indexes will not be copied). The function of INSERT INTO is to insert source data into an existing table. You can use it to merge data. If you want to update existing records, 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

When data is imported and exported between heterogeneous databases, the situation becomes much more complicated. The first thing to solve is how to open a non-SQL Server database.

SQL Server provides two functions that can open and operate various types of databases based on the OLE DB Provider of these databases. These two functions are OPENDATASOURCE and OPENROWSET. Their functions are basically the same, with two main differences.

(1) The calling methods are different.

OPENDATASOURCE has two parameters, namely OLE DB Provider and connection string. Using OPENDATASOURCE is only equivalent to referencing a database or service (for databases such as SQL Server and Oracle). To reference the data table or view, you must reference it after OPENDATASOURCE(...).

Query the table1 table in the Access database abc.mdb through OPENDATASOURCE in SQL Server

SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',

< p> 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source=abc.mdb;Persist Security

Info=False')...

table1

< p> OPENROWSET is equivalent to a recordset and can be used directly as a table or view.

Query the table1 table in the Access database abc.mdb through OPENROWSETE in SQL Server

SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'abc.mdb' ;

'admin';'','SELECT * FROM table1')