Use a Transact-SQL statement to import and export data:
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 select * from opendatasource ('Microsoft. jet. OLEDB.4.0 ', 'Data Source = "D: \ db. mdb "; User ID = Admin; Password = ')... student where name = 'I' select * from newTable ------------------------------------------------------ ------------------------------------------- 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 limit 3. insert data in the SQL SERVER table to the Access Table -- ====== ========================================================== ====== Run in SQL SERVER: insert into OpenDataSource ('Microsoft. jet. OLEDB.4.0 ', 'Data Source = "c: \ DB. mdb "; User ID = Admin; Password = ')... table 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 openrowset ('Microsoft. jet. OLEDB.4.0 ', 'c: \ trade. mdb '; 'admin'; '', table name) SELE CT * FROM sqltablenameinsert into OpenDataSource ('Microsoft. jet. OLEDB.4.0 ', 'Data Source = "D: \ db. mdb "; User ID = Admin; Password = ')... student (studentid, name) select staffid, name from staffinsert into OPENROWSET ('Microsoft. jet. OLEDB.4.0 ', 'd: \ db. mdb '; 'admin'; '', Student) select accountid, name from tblbaccountselect * from openrowset ('Microsoft. jet. OLEDB.4.0 ', 'd: \ db. mdb '; 'admin'; '', Student )---------------- ----------------------------------------------------------------------------- 2. Import and export data of SQL server and EXCEL 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 properties = Excel 5.0 ')... [Sheet1 $] The following is a query example. It queries an Excel spreadsheet through the ole db program for Jet. SELECT * FROM OpenDataSource ('Microsoft. jet. OLEDB.4.0 ', 'Data Source = "c: \ Finance \ account.xls"; User ID = Admin; Password =; Extended properties = Excel 5.0 ')... xactionsSELECT * FROM OpenDataSource ('Microsoft. jet. OLEDB.4.0 ', 'Data Source = "D: \ .xls"; User ID = Admin; Password =; Extended properties = Excel 5.0 ')... vib Environment ------------------------------------------------------------------------------------------- ------ 2. Import Excel Data to 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 pro Perties = Excel 5.0 ')... xactions connector 3. Import the data queried in SQL SERVER into an Excel file T-SQL code: EXEC master .. xp_mongoshell 'bcp database name. dbo. table name out c: \ Temp.xls-c-q-S "servername"-U "sa"-P "" 'parameter: S is the SQL server name; U is the user; P is the password EXEC master .. xp_mongoshell 'bcp axzq. dbo. staff out d: staff.xls-c-q-S ". & quot;-U & quot; sa & quot;-P & quot; gazx & quot; Note: You can also export text files and other format instances: EXEC master .. x P_mongoshell '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' EXEC master .. xp_cmdshell 'bcp "select name from axzq .. staff order by name "queryout d: staffName.xls-c-q-S ". "-U" sa "-P" gazx "'in VB6, use ADO to export the EXCEL file code: Dim Cn As New ADODB. connectioncn. open "Driver = {SQL Server}; Server = WEBSVR; DataBase = WebMis; UID = sa; WD = 123;" cn.exe cute "master .. xp_mongoshell 'bcp "SELECT col1, col2 FROM database name. dbo. table Name "queryout E: \ DT.xls-c-Sservername-Usa-ppassword'" comment 4. insert data to Excel in SQL SERVER: -- ===================================================== ================== = 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 (, 3) T-SQL code: insert into opendatasource ('Microsoft. JET. OLEDB.4.0 ', 'Extended Properties = Excel 8.0; Data source = C: \ training \ inventur.xls ')... [Filiale1 $] (bestand, produkt) VALUES (20, 'test ')------------------------------------------ ----------------------------------------------------- Conclusion: using the preceding statements, we can easily convert data in SQL SERVER, ACCESS, and EXCEL spreadsheet software, which provides us with great convenience! EXEC master .. xp_mongoshell 'bcp "select OrderID, ShipName, ShipAddress, ShipCity, ShipRegion, ShipPostalCode, ShipCountry from Northwind. dbo. orders "queryout" d: \ Oreders.txt "-t" | "-c-q-S" 127.0.0.1 "-U" sa "-P" "'select * FROM OpenDataSource ('Microsoft. jet. OLEDB.4.0 ', 'Data Source = "xxxx.xls"; Extended Properties = "Excel 8.0"; IMEX = 1; Persist Security Info = false ')... [a1 $] 2. Import and export data from SQL SERVER and EXCEL 1. Query Excel Data in ERVER: -- ===================================================== ================== SELECT * FROM OpenDataSource ('Microsoft. jet. OLEDB.4.0 ', 'Data Source = "c: \ book1.xls"; User ID = Admin; Password =; Extended properties = Excel 5.0 ')... [Sheet1 $] The following is a query example. It queries an Excel spreadsheet through the ole db program for Jet. SELECT * FROM OpenDataSource ('Microsoft. jet. OLEDB.4.0 ', 'Data Source = "c: \ Finance \ account.xls"; User ID = Admin; Password =; Extended properties = Excel 5.0 ')... xactionsSELECT * FROM OpenDataSource ('Microsoft. jet. OLEDB.4.0 ', 'Data Source = "d: \ staff.xls"; User ID = Admin; Password =; Extended properties = Excel 5.0 ')... staff 2. Import Excel Data to SQL server: -- ===================================================== ================== SELECT * into newtableFROM OpenDataSource ('Microsoft. jet. OLEDB.12.0 ', 'Data Source = "c: \ book1.xls"; User ID = Admin; Password =; Extended properties = Excel 5.0 ')... [Sheet1 $] instance: SELECT * into newtableFROM OpenDataSource ('Microsoft. jet. OLEDB.12.0 ', 'Data Source = "c: \ Finance \ account.xls"; User ID = Admin; Password =; Extended properties = Excel 5.0 ')... xactions export 3. Export the data queried in SQL SERVER into an Excel file -- ================== ================================== T-SQL code: EXEC master .. xp_mongoshell 'bcp database name. dbo. table name out c: \ Temp.xls-c-q-S "servername"-U "sa"-P "" 'parameter: S is the SQL server name; U is the user; P is password Description: You can also export text files and other format instances: EXEC master .. xp_mongoshell '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' in VB6, use ADO to export the EXCEL file code: Dim cn As New ADODB. connectioncn. open "Driver = {SQL Server}; Server = WEBSVR; DataBase = WebMis; UID = sa; WD = 123;" cn.exe cute "master .. xp_mongoshell 'bcp "SELECT col1, col2 FROM database name. dbo. table Name "queryout E: \ DT.xls-c-Sservername-Usa-ppassword'" comment 4. insert data to Excel in SQL SERVER: -- ===================================================== ================== 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 (, 3) T-SQL code: insert into opendatasource ('Microsoft. JET. OLEDB.4.0 ', 'Extended Properties = Excel 8.0; Data source = C: \ training \ inventur.xls ')... [Filiale1 $] (bestand, produkt) VALUES (20, 'test ') -- enable xp_cmdshell -- SQL Server blocked access to procedure 'xp _ cmdshell' sp _ configure 'show advanced options', 1goreconfiguregosp_configure 'xp _ cmdshell ', 1goreconfigurego -- enable sp_OACreate -- SQL Server blocked access to procedure 'sys. sp_OACreate 'SP _ configure 'show advanced options', 1; goreconfigure; gosp_configure 'ole automation procedures ', 1; goreconfigure; gosp_configure 'ad Hoc Distributed Queries', 1; goreconfigurego