SQL server & excel

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 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:
-- ===================================================== ====================
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
Bytes -------------------------------------------------------------------------------------------------

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 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: 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:
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:
-- ===================================================== ====================
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:
INSERT
OPENDATASOURCE ('Microsoft. JET. OLEDB.4.0 ',
'Extended Properties = Excel 8.0; Data source = C: \ training \ inventur.xls ')... [Filiale1 $]
(Bestand, produkt) VALUES (20, 'test ')
Bytes -------------------------------------------------------------------------------------------------

Use OPENROWSET:

-- If the table to be imported already exists
Insert into Table select * from
OPENROWSET ('Microsoft. JET. OLEDB.4.0'
, 'Excel 5.0; HDR = YES; DATABASE = c: \ test.xls ', sheet1 $)

-- If you import data and generate a table
Select * into table from
OPENROWSET ('Microsoft. JET. OLEDB.4.0'
, 'Excel 5.0; HDR = YES; DATABASE = c: \ test.xls ', sheet1 $)

/* ===================================================== ===================================== */
-- Export data from the SQL database to Excel. If the Excel file already exists and the header has been created based on the data to be received, you can simply use it:
Insert into OPENROWSET ('Microsoft. JET. OLEDB.4.0'
, 'Excel 5.0; HDR = YES; DATABASE = c: \ test.xls ', sheet1 $)
Select * from table

-- If the Excel file does not exist, you can use BCP to import Excel-like files. Note the case sensitivity:
-- Export tables
EXEC master.. xp_mongoshell 'bcp database name. dbo. Table name out "c: \ test.xls"/c/S "server name"/U "username"-P "password "'

-- Export Query Information
EXEC master .. xp_cmdshell 'bcp "SELECT au_fname, au_lname FROM pubs .. authors order by au_lname "queryout" c: \ test.xls "/c/S" server name "/U" username "-P" password "'

/* -- Description:
C: \ test.xls is the Excel file name for import/export. The file must be on the SQL server or the shared directory accessible by the SQL server.
Sheet1 $ is the worksheet name of the Excel file. You must add $ to use it normally.
--*/