How to import and export data using Transact-SQL

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, insert all data in table 1 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) Using insert into and UPDATE to INSERT and UPDATE data 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 to 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.

To UPDATE table1, you can use the UPDATE statement UPDATE table1 SET table1.f1 = table2.f1, table1.f2 = table2.f2 FROM table2 WHERE table1.f1 = table2.f1 to combine the preceding two insert into and UPDATE statements to run them together, you can INSERT a record if it does not exist in Table 1 and UPDATE the record if it exists. However, you must put UPDATE before insert, otherwise, the number of UPDATE records is the total 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 * INTO authors1 from opendatasource ('sqloledb', 'Data Source = 192.168.18.252; User ID = sa; Password = abc'). pubs. dbo. authors

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 replace the above OPENDATASOURCE with openrowset insert into openrowset ('sqloledb', '123. 168.18.252 '; 'sa'; 'abc', 'select * from test. dbo. kk ') SELECT * FROM pubs. dbo. authors

Note the following 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

Alternatively, use openrowset select * from openrowset ('Microsoft. Jet. OLEDB.4.0 ', 'c: data. mdb'; 'admin'; '', 'select * FROM table1 ')

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]

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

Alternatively, use openrowset insert into openrowset ('Microsoft. JET. OLEDB.4.0 ', 'text; DATABASE = c:', [data # txt]) SELECT * FROM text1

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 [. dbf] ') export data
Insert into openrowset ('msdasql. 1 ', 'driver = Microsoft Visual FoxPro Driver; SourceDB = c: db; SourceType = dbf', 'select * FROM. 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 $]

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.