Easy import and export between SQLServer, Access, and Excel Data Tables

In SQLSERVER20002005, in addition to using DTS for data import and export, we can also use a Transact-SQL statement to import and export data. In a Transact-SQL statement, we mainly use the OpenDataSource function and OPENROWSET function. For more information about functions, see SQL online help. The following methods can be used:

In SQL server 2000/2005, in addition to using DTS for data import and export, you can also use a Transact-SQL statement to import and export data. In a Transact-SQL statement, we mainly use the OpenDataSource function and OPENROWSET function. For more information about functions, see SQL online help. The following methods can be used:

In SQL SERVER 2000/2005, in addition to using DTS for DataImportExportYou can also use the Transact-SQL statement.ImportExportOperation. In a Transact-SQL statement, we mainly use the OpenDataSource function and OPENROWSET function. For more information about functions, see SQL online help. The following methods can be used to easilyImplementationSQL SERVER, ACCESS, EXCEL Data conversion, detailed descriptions are as follows:

I. SQL SERVER and ACCESS dataImportExport

Regular dataImportExport:

Use the DTS wizard to migrate your Access data to SQL Server. You can use these steps:

1. On the Tools menu in SQL SERVER Enterprise Manager, select Data Transformation

2 Services, and then select czdImport Data (ImportData ).

3. In the Choose a Data Source dialog box, select Microsoft Access as the Source, and then type your. mdb database (. mdb File Extension) or browse to find the file.

4. In the Choose a Destination (select target) dialog box, select Microsoft ole db Prov ider for SQL Server, select database Server, and click the required authentication method.

5. In the Specify Table Copy or Query dialog box, click Copy tables ).

6. In the Select Source Tables dialog box, click Select All ). Next, complete.

Execute the Transact-SQL statementImportExport:

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. Set accessImportSQL 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

Ii. SQL SERVER and EXCEL DataImportExport

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

2. Convert Excel DataImportSQL 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

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 alsoExportMultiple formats such as text files

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 in VB6ExportEXCEL 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 '"

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 ')

Note: If a problem occurs when you query an Excel file in SQL Server:
SELECT * from openrowset ('Microsoft. JET. OLEDB.4.0 ', 'excel 8.0; IMEX = 1; HDR = YES; DATABASE = D: \ a.xls', [sheet1 $])
Result prompt:
SQL Server blocks access to the STATEMENT 'openrowset/OpenDatasource 'of the 'ad Hoc Distributed Queries' component because this component has been disabled as part of the Server's security configuration. The system administrator can enable 'ad Hoc Distributed Queries 'by using sp_configure '. For more information about enabling 'ad Hoc Distributed querys', see "peripheral application configurator" in SQL Server books online ".
You can use the following solutions by enabling Ad Hoc Distributed Queries:

Enable Ad Hoc Distributed Queries:
Exec sp_configure 'show advanced options', 1
Reconfigure
Exec sp_configure 'ad Hoc Distributed Queries ', 1
Reconfigure
After use, disable Ad Hoc Distributed Queries:
Exec sp_configure 'ad Hoc Distributed Queries ', 0
Reconfigure
Exec sp_configure 'show advanced options', 0
Reconfigure