Introduction to import and export functions between different databases _mssql

1. Data import and export between SQL Server databases
(1). Use Select to export data
The most widely used SQL Server is to export data through the SELECT INTO statement, which has two features: an empty table based on the field followed by the Select and the table name into which it follows (if the select is *, The structure of the empty table is the same as the structure of the table in which it is referred to, and the data isolated from the select is inserted into this empty table. When using the SELECT INTO statement, the table followed by into must not exist in the database, or it will be an error, and the following is an example of using SELECT INTO.



Suppose you have a table table1, field F1 (int), f2 (varchar (50)).

SELECT * into table2 from table1

This SQL is used to insert the Table1 data into the Table1 after the Table2 table is established, and the * can be changed to F1 or F2 to insert data into the appropriate fields.

SELECT into not only can 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 above statement establishes a table table2 in the database DB2 that the owner is dbo, and the user who is currently logged on when the table is built to DB2 must have permission to build the table in DB2 to establish table2. One thing to note with SELECT INTO is that select into cannot be used with compute, because compute returns a set of recordsets, which can cause a sense of duality (that is, not knowing which table to base an empty table on).

(2). Insert and update data using INSERT INTO and update
SELECT into can only copy data into an empty table, and insert into to insert data from one table or view into another.

INSERT into table1 SELECT * from table2

or INSERT into Db2.dbo.table1 SELECT * from table2

But the INSERT INTO statement above may result in a primary key conflict error (if a field in Table1 is a primary key, it happens that the field in Table2 has the same value as the Table1 field). Therefore, the above statement can be modified to

INSERT into Table1--assume field F1 as primary key

SELECT * from table2 where not EXISTS (select Table1.f1 from table1 where table1.f1=table2.f1)

The function of the above statement is to insert a record F1 in table2 that does not exist in Table1 into table1.

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

By combining the above two insert and UPDATE statements together, you can implement the ability to insert a record when it is not present in Table1, and update it when it exists, but be careful to put the update in front of INSERT into Otherwise, the number of records updated by update will be the sum of the number of Table1 and table2 Records.

2. Import Export data between different types of databases using OPENDATASOURCE and OPENROWSET
With data transfer between heterogeneous databases, you can use the two system functions provided by SQL Server OPENDATASOURCE and OPENROWSET.

OPENDATASOURCE can open any database that supports OLE DB and can make OpenDataSource the name of the table followed by SELECT, UPDATE, insert, and delete. Such as

SELECT * from OpenDataSource (' SQLOLEDB ', ' datasource=192.168.1.113; User Id=sa; password=123456 '). pubs.dbo.authors

SELECT * from OpenDataSource (SQLOLEDB, Data source=192.168.18.252; User Id=sa; password=test). pubs.dbo.authors

The function of this statement is to query 192.168.18.252 the authors table in the SQL Server database pubs in this machine. As you can see from this statement, the OPENDATASOURCE has two parameters, the first parameter is provider_name, which represents the PROGID name of the OLE DB provider that is used to access the data source. The provider_name data type is char and has no default value. The second parameter is the connection string, depending on the OLE DB provider (if you do not know the connection string for the OLE DB provider you are using, you can use Delphi, visual The ADO controls in development tools such as studio automatically generate the corresponding connection strings.

The OPENROWSET function is similar to the OPENDATASOURCE function, except that it can query the tables in the database while the database is open, such as the following statement

OPENROWSET (MSDASQL.1, Driver=microsoft Visual FoxPro Driver; Sourcedb=c:\db; SOURCETYPE=DBF, SELECT * from [B.DBF])

The last parameter Query FoxPro table B.DBF, the reader can filter the B.DBF through the Where condition. If you use INSERT INTO, SELECT, and OpenDataSource or OPENROWSET, you can import data into and out of SQL Server databases and other types of databases. The following describes how to use these two functions to import data into and out of a SQL Server database and other types of databases.

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

SELECT * INTOauthors1 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 for this service management. OpenDataSource (...) When you use INSERT INTO The table followed must exist.

You can also change the above OpenDataSource to OPENROWSET

INSERT into OPENROWSET (SQLOLEDB,192.168.18.252;SA;ABC, select * to TEST.DBO.KK) SELECT * FROM Pubs.dbo.authors

Use OPENROWSET to note that the middle of the 192.168.18.252;SA;ABC is ";" instead of ",". Both OPENDATASOURCE and OPENROWSET do not accept parameter variables.

(2). Data import export 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 an Access database is called microsoft.jet.oledb.4.0, and you need to be aware of operating a non-SQL Server database in OpenDataSource (...) Use "..." instead of "." When referring to tables in the database later.

(3). Data import export between SQL Server database and text file.
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 a partial field, you can use the

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-packing directory into the Text1 table, where the file name "." To use "#" instead. When exporting to text, not only does the text file exist, but the first row must be one to the fields to which you want to export the table.

(4). Data import export 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 (...) The b.dbf in is used [...] Enclose, is to when DBF filename has a space and so on characters without error, if not these special characters, you can [...] Remove

(5). Data import export 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

Here a.dbf cannot use [...] Enclosed, otherwise an error (this is determined by driver).

(6). Data import export between SQL Server database and Excel file
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

You must have fields that correspond to Excel tables in the Book1.xls Sheet1, or you will get an error.

The above discussion discusses how to use Transact-SQL for data import and export between several commonly used databases and the database. The ability to register other types of databases into SQL Server is also available in SQL Server, so you can use the tables in these registered databases as you would with SQL Server database tables.

EXEC sp_addlinkedserver access,ole DB Provider for Jet, microsoft.jet.oledb.4.0, C:\data.mdb

The above SQL uses stored procedure sp_addlinkedserver to register an Access database, and we can query the Table1 in Data.mdb using the following statement in SQL Server.

SELECT * from Access...table1

This makes it easy to query the tables in the Access database and, if you want to import table1, you can use the SELECT * into the table2 from Access...table1. If you want to delete a registered database connection, use the following statement.

EXEC sp_dropserver Access

Using Transact-SQL can not only import the exported data to the SQL Server database, but also allow any two types of databases to import and export data to each other. Take access and Excel as an example.

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)

The above SQL statement inserts data from the Table1 table of an Access database into the Sheet1 form in the Excel file Book1.xls.