Import and export data between SQL Server databases

Document directory

• (1). Use select into to export data
• (1) import and export data between the SQL Server database and the SQL Server database.

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 * INTO authors1
From opendatasource (
'Sqlodb ',
'Data Source = 192.168.18.252; User ID = sa; Password = abc'
). Pubs. dbo. authors
Export data

Insert into opendatasource (
'Sqlodb ',
'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 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
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.