Import and export data between databases

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 SELECT statement is followed by *, 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 problems: I don't 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 INTOTable1SELECT*FROMTable 2
  
  
Or
  
  
INSERT INTODb2.dbo. table1SELECT*FROMTable 2
 
 

However, the preceding insert into statement may generate a primary key conflict error. If a field in table 1 is a primary key, the values of this field in Table 2 are the same as those of the 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 do not know 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  
 
 

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 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*INTOAccess
  
  
FROMOPENDATASOURCE (
  
  
'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*FROMOPENROWSET ('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 INTOOPENDATASOURCE ('Microsoft. JET. OLEDB.4.0','Text; DATABASE = c :"') [Data # txt]SELECT*FROMText1
  
  
Or use OPENROWSET
  
  
INSERT INTOOPENROWSET ('Microsoft. JET. OLEDB.4.0','Text; DATABASE = c :"', [Data # txt])SELECT*FROMText1
 
 

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, the error 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_addlinkedserver  
  
  
'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 for data import and export, you can easily put these Transact-SQL statements into a client program such as delphi and c ), this allows you to easily write your own Database Import and Export tools.

Source: http://www.cnblogs.com/yalong_xiang/archive/2011/03/17/1986715.html

1. How to batch import data in the SQL Server database
2. SQL Database Import using excel
3. Export and Import sybase Data Tables
4. Implementation of batch data import in MySQL