SQL operations on excel tables (query, import, and insert)

Copy codeThe Code is as follows: -- Configure permissions
EXEC sp_configure 'show advanced options', 1;
GO
RECONFIGURE;
GO
EXEC sp_configure 'ad Hoc Distributed Queries ', 1;
GO
RECONFIGURE;
GO

SQL SERVER and EXCEL Data Import and Export
1. query Excel Data in SQL SERVER:
-- ===================================================== ====================Copy codeThe Code is as follows: 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 an example of a query. It queries an Excel spreadsheet through 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 properties = Excel 5.0 ')... xactions

Bytes -------------------------------------------------------------------------------------------------
2. Import Excel Data to SQL server:
-- ===================================================== ====================Copy codeThe Code is as follows: 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:Copy codeThe Code is as follows: 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

Bytes -------------------------------------------------------------------------------------------------
3. Export the data queried in SQL SERVER into an Excel file
-- ===================================================== ====================
T-SQL code:Copy codeThe Code is as follows: EXEC master .. xp_mongoshell 'bcp database name. dbo. Table name out c: \ Temp.xls-c-q-S "servername"-U "sa"-P ""'

Parameter: S indicates the SQL server name, U indicates the user, and P indicates the password.
Note: You can also export text files and other formats.
Instance:Copy codeThe Code is as follows: EXEC master .. xp_mongoshell 'bcp saletesttmp. dbo. CusAccount out c: \ temp1.xls-c-q-S "pmserver"-U "sa"-P "sa "'
EXEC master .. xp_mongoshell '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:Copy codeThe Code is As follows: 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 '"

Bytes ------------------------------------------------------------------------------------------------
4. insert data to Excel in SQL SERVER:
-- ===================================================== ====================Copy codeThe Code is as follows: 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:Copy codeThe Code is as follows: INSERT
OPENDATASOURCE ('Microsoft. JET. OLEDB.4.0 ',
'Extended Properties = Excel 8.0; Data source = C: \ training \ inventur.xls ')... [Filiale1 $]
(Bestand, produkt) VALUES (20, 'test ')