SQL Direct Operations Excel table (query/import/insert) _mssql

Copy Code code 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

Data import export for SQL SERVER and Excel
1. Query Excel data in SQL Server:
-- ======================================================

Copy Code code 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 that queries an 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 properties=excel 5.0 ') ... xactions

-------------------------------------------------------------------------------------------------
2. Import Excel data into SQL Server:
-- ======================================================

Copy Code code 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 Code code 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

-------------------------------------------------------------------------------------------------
3, the SQL Server query to the data into an Excel file
-- ======================================================
T-SQL code:

Copy Code code as follows:

EXEC Master.. xp_cmdshell ' bcp library name. dbo. Table name out c:\Temp.xls-c-q-s "servername"-U "sa"-P "" "

Parameter: s is SQL Server name; u is user; p is a password
Description: You can also export a variety of formats such as text files
Instance:

Copy Code code as follows:

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"

Applying ADO to export Excel file code in VB6:

Copy Code code as follows:

Dim cn as New ADODB. Connection
Cn.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. Table name "Queryout E:\DT.xls-c-sservername-usa-ppassword '"

------------------------------------------------------------------------------------------------
4. Insert data into Excel in SQL Server:
-- ======================================================

Copy Code code 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 Code code as follows:

INSERT into
OpenDataSource (' Microsoft.Jet.OLEDB.4.0 ',
' Extended properties=excel 8.0;data source=c:\training\inventur.xls ') ... [filiale1$]
(Bestand, Produkt) VALUES (' Test ')