Import and export of SQL Server, Excel, and access data tables (NOTE: Refer to the overview of Boyuan. Net)

 

We know that SQL Server integrates the data import and export tool. How can we use SQL scripts to easily operate data in access and excel? Let's take a look:

I. Import and export data from SQL Server and Excel,

A. query data in Excel.

Select *
From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "F: \ test.xls"; user id = admin; Password =; extended properties = Excel 5.0 ')... [sheet1 $]

Simple analysis:

OpenDataSource, A function in the system, if not very clear can point this, http://msdn.microsoft.com/zh-cn/library/ms179856.aspx,

A simple understanding is:Provide a special connectionHere, we can understand it as opening access and connecting to excel.

The above script can also be written as follows,

Select *
From OpenDataSource ('Microsoft. Jet. oledb.4.0 ',
'Data source = "F: \ test.xls"; extended properties = Excel 5.0 ')... sheet1 $

The execution result is as follows:

B. 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 $]

C. Import the data queried in SQL Server into an Excel file

T-SQLCode:

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

 

II,SQL Server and accss Data Import and Export

A. 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

B. Import access to SQL 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

C. 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

If the following error occurs during running, for example:

Message 15281, level 16, state 1, 1st rows
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 ".

There are two solutions (I know now)

1 You can use the following solutions by enabling 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

 

The two are in "start" -- "all"Program"--" "Microsoft SQL Server 2005" "configuration tool" -- "" SQL Server peripheral application configurator ". After opening the tool, see:

 

Then click, such:

 

Then enable OpenRowSet and OpenDataSource to enable "hook" and click OK, as shown in the following figure: