Stored Procedure Syntax notes

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



Suppose there is a table table1, field F1 (int), f2 (varchar (50)).

SELECT * into table2 from table1

After establishing the Table2 table, this SQL language inserts all table1 data into table1, and you can change the * 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 database DB2 that the owner is the 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 that will cause duality (that is, you do not know which table to create empty tables from).

(2). Inserting and updating data using INSERT into and update
SELECT into can only copy data into an empty table, and insert into lets you insert data from one table or view into another table.

INSERT into table1 SELECT * from table2

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

However, 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 value in this field in Table2 is the same as the value of this field in Table1). Therefore, the above statement can be modified to

INSERT into Table1--suppose field F1 primary key

SELECT * from Table2 where isn't EXISTS (select table1.f1 from table1 where table1.f1=table2.f1)

The function of the above statement is to insert records F1 in table2 that do 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 into and UPDATE statements together, you can implement the ability to insert and update when the record is not present in Table1, but be careful to place the update in front of insert Otherwise, the number of records updated by update will be the sum of the number of Table1 and table2 Records.

2. Import and export data between different types of databases using OPENDATASOURCE and OPENROWSET
For data transfer 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 you can use OpenDataSource as 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 the 192.168.18.252 Authors table in the SQL Server database in pubs in this machine. As you can see from this statement, OPENDATASOURCE has two parameters, and the first parameter is provider_name, which represents the name of the PROGID of the OLE DB provider used to access the data source. The data type of the provider_name is char and has no default value. The second parameter is the connection string, which differs depending on the OLE DB provider (if you are unsure of 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 appropriate connection string).

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

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

The last parameter queries the FoxPro table B.DBF, where the reader can filter the B.DBF by a where condition. If you use INSERT INTO, SELECT into, and OpenDataSource or OPENROWSET, you can import and export data between SQL Server databases and other types of databases. Here's how to use these two functions to import and export data between a SQL Server database and other types of databases.

(1). Data import and 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

Exporting 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 (...) A service that can be understood as SQL Server,. pubs.dbo.authors is a table authors for a database that is managed by this service. OpenDataSource (...) when using 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 * from TEST.DBO.KK) SELECT * FROM Pubs.dbo.authors

Use OPENROWSET to be aware 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 and export between SQL Server database and 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)

Exporting 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

To open an OLE DB provider for an Access database called microsoft.jet.oledb.4.0, you need to be aware of the operation of a non-SQL Server database in OpenDataSource (...). Use "..." instead of "." When referencing tables in the database later.

(3). Data import and 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]

Exporting 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 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 "." in the file name. To use "#" instead. When exporting to text, not only does the text file exist, but the first line must be one to the field to which you want to export the table.

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

SELECT * to dbase from OPENROWSET (MICROSOFT. JET. oledb.4.0, DBase III; Hdr=no;imex=2;database=c:\,select * FROM [B.DBF])

Exporting 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 in the use of [...] In this case, the DBF file name will not be faulted if there are spaces and so on, if there are no such special characters, the [...] Remove

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

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

Exporting data

INSERT into OPENROWSET (MSDASQL.1, Driver=microsoft Visual FoxPro Driver; Sourcedb=c:\db; Sourcetype=dbf,select * from A.DBF) SELECT * from FoxPro

A.DBF cannot use [...] in this place. Error (This is determined by driver).

(6). Data import and 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$]

Exporting data

INSERT into OpenDataSource (MICROSOFT. JET. Oledb.4.0,excel 5.0;database=c:\book1.xls) ... [sheet1$] SELECT * from Excel

There must be a field in the Book1.xls Sheet1 that corresponds to the Excel table, otherwise an error occurs.

The above 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 in SQL Server is also available in SQL Server, so that 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 SQL above uses the stored procedure sp_addlinkedserver to register an Access database, and we can use the following statement in SQL Server to query the table1 in Data.mdb.

SELECT * from Access...table1

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

EXEC sp_dropserver Access

You can use Transact-SQL not only to import and export data to a SQL Server database, but also to import and export data between any two types of databases. 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 the Access database into the Sheet1 form in the Excel file Book1.xls.

Stored Procedure Syntax notes