SQL code
SQL Server and Excel Data Import and 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 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 syntax 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 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' use ADO to export the Excel file code in VB6: 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 ')