SQL Server, access, and Excel Data Import and Export

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 newtable from OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "C: \ dB. mdb "; user id = admin; Password = ')... table Name -------------------------------------------------------------------------------------------------

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 test insert into OpenRowSet ('Microsoft. jet. oledb.4.0 ', 'c: \ trade. MDB '; 'admin'; '', table name) Select * From sqltablename example -------------------------------------------------------------------------------------------------

Ii. Import and export data from 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 ')... xactions Lifecycle -------------------------------------------------------------------------------------------------

2. Import Excel Data to SQL Server: -- ===================================================== ================== select * into newtable from OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "C: \ book1.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... [sheet1 $] instance: Select * into newtable from OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "C: \ finance \ account.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... xactions Lifecycle -------------------------------------------------------------------------------------------------

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. connection CN. 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') Values ')-------------------------------------------------------------------------------------------------

Summary: with the above statements, we can easily convert data in SQL Server, access, and Excel spreadsheet software, which provides us with great convenience!