Database administrators who are familiar with SQL Server 2000 know that DTS can be exported for data import, in fact, we can also use Transact-SQL statements to import export operations. In the Transact-SQL statement, we mainly use the OpenDataSource function, the OpenRowSet function, for details about the function, please refer to the SQL online help. With the following method, SQL Server, Access, Excel data conversion can be easily implemented, and the detailed description is as follows: 1. SQL Server and Access data import export: Use the DTS wizard to migrate your Access data to SQL Server You can use these steps: ○ 1 On the Tools menu in the SQL Server Enterprise Manager, select Data Transformation ○ 2Services (Data Conversion Service), then select CZDIMPORT DATA. ○ 3 Select Microsoft Access As The Source in the Choose A Data Source dialog box, then type your .mdb database (.mdb file extension) file name or by browsing the file. ○ 4 In the Choose A Destination dialog box, select Microsoft Ole DB ProvIdeer for SQL Server, select the Database Server, and then click the necessary verification mode. ○ 5 In Specify Table Copy or Query dialog box, click Copy Tables. ○ 6 In the SELECT SOURCE TABLES dialog box, click SELECT ALL (all selected). Next, complete.
Transact-SQL statement for import: 1. Query Access data in SQL Server: Select * from OpenDataSource ('Microsoft.jet.OleDb.4.0', 'data source = "c: /db.mdb"; user ID = admin Password = ') ... Table name 2. Import Access to SQL Server Run in SQL Server: Select * INTO NewTableFrom OpenDataSource (' Microsoft.jet.OleDb.4.0 ',' Data Source = "C: /Db.mdb "; User ID = admin; password = ') ... Table name 3. Insert the data in the SQL Server table into the Access table run in SQL Server: INSERT INTO OPENDATASOURCE (' Microsoft.jet.OleDb.4.0 ', 'Data source = "c: /db.mdb"; user ID = admin; password =') ... Name (column name 1, column name 2) SELECT column name 1, column name 2 from SQL Table Instance: Insert INTO OpenRowSet ('Microsoft.jet.OleDb.4.0', 'c: /db.mdb'; 'admin'; '', test) Select ID, Name from testinsert into.0 ', 'c: /trade.mdb'; 'admin'; '', table name) Select * from Sqltablename II, SQL Server and Excel Data Import Export 1, query Excel data in SQL Server: SELECT * from OpenDataSource ('Microsoft .Jet.oledb.4.0 ',' data source = "c: /book1.xls"; user ID = admin; password =; extended profout = Excel 5.0 ') ... [Sheet1 $] Below is an example of a query, it Query the Excel spreadsheet by using the OLE DB provider for Jet.
Select * from OpenDataSource ('microsoft.jet.oledb.4.0', 'data source = "c: /finance/account.xls"; user ID = admin; password =; extended profout = excel 5.0') ... XActionS2, Import Excel's data into SQL Server: select * INTO newTableFrom OpenDataSource ('microsoft.jet.oledb.4.0', 'data source = "c: /book1.xls"; user ID = admin; password =; extended Properties = Excel 5.0 ') ... [Sheet1 $] instance: select * INTO newTableFrom OpenDataSource (' microsoft.jet.OleDb.4.0 ',' data source = "c: /finance/account.xls"; user ID = admin; password =; Extended Properties = Excel 5.0 ') ... XActionS3, the data queried in SQL Server into an Excel file T-SQL code: exec master..xp_cmdshell' bcp library name .dbo. Table name Out C: / Temp. Xls -c -q -s "servername" -u "SA" -p "" parameter: s is the SQL server name; u is user; P is password description: You can also export a variety of formats such as text files: Exec Master .. xp_cmdshell 'bcp saletesttmp.dbo.cusaccount out c: /temp1.xls -c -q -s "pmserver" -u "sa" -p "sa"' exec master..xp_cmdshell 'bcp "SELECT AU_FNAME, AU_LNAME FROM Pubs..AUTHORS ORDER BY AU_LNAME "Queryout C: / Authors.xls -c -sserverName -usa -ppassword" Apply ADO Export EXCEL File Code in VB6: DIM CN AS NEW AdoDb.connectionCn.open "driver = {SQL Server}; server = WebSVR; Database = Webmis; UID = SA; WD = 123;" cn.execute "master..xp_cmdshell 'bcp" SELECT COL1, Col2 from library name. Dbo . Name "Queryout E: /DT.XLS -C -SSERVERNAME-ISA-PPASSWORD '" 4, in SQL Server to Excel inserted data: INSERT INTO OpenDataSource (' Microsoft.jet.OleDb.4.0 ',' Data Source = "c: /TEMP.XLS"; user ID = admin; password =; extended Properties = Excel 5.0 '
... Table1 (A1, A2, A3) VALUES (1, 2, 3) T-SQL code: INSERT INTO OPENDATASOURCE ('Microsoft.jet.OleDb.4.0', 'Extended Properties = Excel 8.0; Data Source = C : /TRAINING/INVENTUR.XLS ') ... [Filia1 $] (Bestand, Product, Product) Values (20,' Test ') Summary: Using the above statement, we can easily use the SQL Server, Access, and Excel spreadsheet software. The data is converted, providing us with great convenience! Quote from: http://www.vikosoft.net/trackback.asp? TBID = 336
Author: Li root
