Data Import and Export between SQLServer Heterogeneous Databases

This article discusses how to import and export data between homogeneous and heterogeneous databases through Transact-SQL and the system functions OPENDATASOURCE and OPENROWSET, and provides detailed examples for reference.

1. Import and export data between SQL Server databases (1). Use select into to export data

The most widely used SQL Server is to export data using the SELECT INTO statement. The SELECT INTO statement has two functions at the same time: create an empty table based on the fields followed by the SELECT statement and the table names followed by the INTO Statement (if the field is * after the SELECT statement, the structure of the empty table is the same as that of the table referred to by the FROM statement ); insert the data from the SELECT statement to this empty table. When the select into statement is used, the table after INTO must not exist in the database; otherwise, an error occurs. The following is an example of using select.

Assume that there is a table table1 with the fields f1 (int) and f2 (varchar (50 )).

SELECT * INTO table2 FROM table1

After creating a table 2 table, this SQL statement inserts all Table 1 data into Table 1. You can also change * to f1 or f2 to insert data to an appropriate field.

Select into can not only create tables in the same data, but also create tables in different SQL Server databases.

USE db1

SELECT * INTO db2.dbo. table2 FROM table1

The preceding statement creates Table 2 with the dbo owner in the database db2. When creating a table to db2, the user currently logged on must have the permission to create a table in db2 to create table 2. When using select into, note that select into cannot be used with COMPUTE because COMPUTE returns a set of records, this will lead to two errors (that is, you do not know which table to create an empty table ).

(2) INSERT and UPDATE data using insert into and UPDATE

Select into can only copy data to one empty table, while insert into can INSERT data from one table or view to another table.

Insert into table1 SELECT * FROM table2

Or insert into db2.dbo. table1 SELECT * FROM table2

However, the preceding insert into statement may generate a primary key conflict error (if a field in table 1 is a primary key, it happens that the value of this field in table 2 is the same as the value of this field in table 1 ). Therefore, the preceding statement can be modified

Insert into table1 -- assume that field f1 is the primary key.

SELECT * FROM table2 where not exists (SELECT table1.f1 FROM table1 WHERE table1.f1 = table2.f1)

The preceding statement inserts records that do not exist in table 1 in Table 2 into table 1.

You can use the UPDATE statement to UPDATE table 1.

UPDATE table1 SET table1.f1 = table2.f1, table1.f2 = table2.f2 FROM table2 WHERE table1.f1 = table2.f1

By combining the two insert into and UPDATE statements, you can INSERT and UPDATE records if they do not exist in table 1, however, you must put UPDATE before insert into. Otherwise, the number of records updated by UPDATE will be the sum of the number of records in table1 and table2.

2. Use OPENDATASOURCE and OPENROWSET to import and export data between different types of databases

For data transmission between heterogeneous databases, you can use the two system functions OPENDATASOURCE and OPENROWSET provided by SQL Server.

OPENDATASOURCE can open any database that supports ole db and use OPENDATASOURCE as the name of the table after SELECT, UPDATE, INSERT, and DELETE. For example

SELECT * from opendatasource (SQLOLEDB, Data Source = 192.168.18.252; User ID = sa; Password = test). pubs. dbo. authors

This statement is used to query the authors table in the SQL Server database pubs on the machine 192.168.18.252. From this statement, we can see that OPENDATASOURCE has two parameters. The first parameter is provider_name, which indicates the name of the PROGID of the ole db provider used to access the data source. The data type of provider_name is char, with no default value. The second parameter is the connection string, which varies depending on the ole db Provider (if you are not clear about the connection string of the ole db Provider you are using, you can use the ADO control in development tools such as delphi and visual studio to automatically generate the corresponding connection string ).

The OPENROWSET function is similar to the OPENDATASOURCE function, but it can query tables in the database while opening the database, as shown in the following statement:

OPENROWSET (MSDASQL.1, Driver = Microsoft Visual FoxPro Driver; SourceDB = c: \ db; SourceType = DBF, SELECT * FROM [B. dbf])

The last parameter is used to query Table B. dbf of foxpro. You can use the where condition to filter B. dbf. If you use insert into, select into, OPENDATASOURCE, or OPENROWSET together, you can import and export data between the SQL Server database and other types of databases. The following describes how to use these two functions to import and export data between the SQL Server database and other types of databases.

(1) import and export data between the SQL Server database and the SQL Server database.

Import Data

SELECT * revoke authors1 from opendatasource (SQLOLEDB, Data Source = 192.168.18.252; User ID = sa; Password = abc). pubs. dbo. authors

Export data

Insert into opendatasource (SQLOLEDB, Data Source = 192.168.18.252; User ID = sa; Password = abc). test. dbo. authors select * from pubs. dbo. authors

In this statement, OPENDATASOURCE (...) can be understood as a service of SQL Server.. pubs. dbo. authors is a table authors of a database managed by this service. When insert into is used, the table followed by OPENDATASOURCE (...) must exist.

You can also change the above OPENDATASOURCE to OPENROWSET.

Insert into openrowset (SQLOLEDB, 192.168.18.252; sa; abc, select * from test. dbo. kk) SELECT * FROM pubs. dbo. authors

Note that when using OPENROWSET, 192.168.18.252; sa; abc indicates ";", rather ",". Both OPENDATASOURCE and OPENROWSET do not accept parameter variables.

(2) import and export data between the SQL Server database and the Access database.

Import Data

SELECT * INTO access from opendatasource (Microsoft. jet. OLEDB.4.0, Provider = Microsoft. jet. OLEDB.4.0; Data Source = c: \ data. mdb; Persist Security Info = False )... table1

Or use OPENROWSET

SELECT * from openrowset (Microsoft. Jet. OLEDB.4.0, c: \ data. mdb; admin;, SELECT * FROM table1)

Export data

Insert into opendatasource (Microsoft. jet. OLEDB.4.0, Provider = Microsoft. jet. OLEDB.4.0; Data Source = c: \ data. mdb; Persist Security Info = False )... table1 SELECT * FROM access

The ole db Provider that opens the access database is Microsoft. jet. OLEDB.4.0, note that operations on non-SQL Server databases in OPENDATASOURCE (...) use "... ", not". ".

(3) import and export data between the SQL Server database and text files.

Import Data

SELECT * INTO text1 from opendatasource (MICROSOFT. JET. OLEDB.4.0, Text; DATABASE = c: \)... [data # txt]

Export data

Insert into opendatasource (MICROSOFT. JET. OLEDB.4.0, Text; DATABASE = c: \)... [data # txt] SELECT * FROM text1

Or use OPENROWSET

Insert into openrowset (MICROSOFT. JET. OLEDB.4.0, Text; DATABASE = c: \, [data # txt]) SELECT * FROM text1

If you want to insert some fields, you can use

Insert into openrowset (MICROSOFT. JET. OLEDB.4.0, Text; DATABASE = c: \, SELECT aa FROM [data # txt]) SELECT aa FROM text1

The function of this SQL statement is to import the data.txt file of the C-root directory to the text1 table. Here, "." In the file name must be replaced. When exporting data to a text file, not only must the text file exist, but the first line must be the same as the fields in the table to be exported.

(4) import and export data between the SQL Server database and the dbase database.

Import Data

SELECT * INTO dbase from openrowset (MICROSOFT. JET. OLEDB.4.0, dBase III; HDR = NO; IMEX = 2; DATABASE = C: \, SELECT * FROM [B. dbf])

Export data

Insert into openrowset (MICROSOFT. JET. OLEDB.4.0, dBase III; HDR = NO; IMEX = 2; DATABASE = C: \, SELECT * FROM [B. dbf]) SELECT * FROM dbase

OPENROWSET (...) b. dbf uses [...] to prevent errors when the dbf file name contains characters such as spaces. If you do not have these special characters, you can [...] remove

(5) import and export data between the SQL Server database and the foxpro database.

Import Data

SELECT * INTO foxpro from openrowset (MSDASQL.1, Driver = Microsoft Visual FoxPro Driver; SourceDB = c: \; SourceType = DBF, SELECT * FROM [a. dbf])

Export data

Insert into openrowset (MSDASQL.1, Driver = Microsoft Visual FoxPro Driver; SourceDB = c: \ db; SourceType = DBF, SELECT * FROM a. dbf) SELECT * FROM foxpro

In this case, a. dbf cannot be included in [...]. Otherwise, an error occurs (this is determined by the driver ).

(6) import and export data between SQL Server database and excel files

Import Data

SELECT * INTO excel from opendatasource (MICROSOFT. JET. OLEDB.4.0, Excel 5.0; DATABASE = c: \ book1.xls)... [Sheet1 $]

Export data

Insert into opendatasource (MICROSOFT. JET. OLEDB.4.0, Excel 5.0; DATABASE = c: \ book1.xls)... [Sheet1 $] SELECT * FROM excel

The sheet 1 in book1.xls must have fields corresponding to the excel table; otherwise, an error occurs.

The preceding section describes how to use Transact-SQL to import and export data between several common databases and SQL Server databases. SQL Server also provides the function of registering other types of databases to SQL Server, so that you can use these tables in the registered database like using SQL Server database tables.

EXEC sp_addmediaserver access, ole db Provider for Jet, Microsoft. Jet. OLEDB.4.0, c: \ data. mdb

The preceding SQL uses the Stored Procedure sp_addmediaserver to register an access database. We can use the following statement in SQL Server to query table1 in data. mdb.

SELECT * FROM access... table1

In this way, you can easily query tables in the access database. To import table 1, you can use SELECT * INTO table2 FROM access... table1. To delete a registered database connection, use the following statement.

EXEC sp_dropserver access

Using Transact-SQL not only allows you to import and export data to the SQL Server database, but also allows any two types of databases to import and export data to and from each other. Take access and excel as examples.

Insert into opendatasource (MICROSOFT. JET. OLEDB.4.0, Excel 5.0; DATABASE = c: \ book1.xls )... [Sheet1 $] SELECT * from openrowset (Microsoft. jet. OLEDB.4.0, c: \ data. mdb; admin;, SELECT * FROM table1)

Insert the data in table 1 of the accessdata database to the sheet 1 in the Excel file book1.xls.

Using Transact-SQL to import and export data allows you to conveniently put these Transact-SQL statements into client programs (such as delphi and c ), this allows you to easily write your own Database Import and Export tools.