Data conversion between SQL codesql server and access and Excel

Database Administrators familiar with SQL Server 2000 know that DTS can import and export data. In fact, we can also use Transact-SQL statements 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. You can easily convert SQL Server, access, and Excel data by using the following methods:

I. Import and export data from SQL Server and access

General data import and export:

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 (import data ).
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.

Use a Transact-SQL statement to import and export data:

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
Bytes -------------------------------------------------------------------------------------------------
2. 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
Bytes -------------------------------------------------------------------------------------------------
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
Bytes -------------------------------------------------------------------------------------------------

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

Summary: with the above statements, we can easily convert data in SQL Server, access, and Excel spreadsheet software, which provides us with great convenience!

 

 

 

Message 15281, level 16, state 1, 14th rows
SQL Server blocks the Statement of the 'ad hoc distributed queries 'component.
'Openrowset/OpenDataSource 'access, because this component has been disabled as part of the security configuration of this server. The system administrator can use
Sp_configure enables 'ad hoc distributed queries '. Enable 'ad hoc distributed
For more information about queries, see "peripheral application configurator" in SQL Server books online ".

The SQL Server blocks the 'ad hoc distributed queries'
Statement 'openrowset/OpenDataSource'
Because this component has been disabled as part of the server security configuration. The system administrator can use sp_configure to enable 'ad hoc distributed
Queries '. Enable 'ad hoc distributed queries'
<Http://www.cnblogs.com/wayne-ivan/archive/2008/01/07/1028759.html>

SQL Server blocks access to component \ 'ad hoc distributed queries \'

An error occurred while querying the 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:
The SQL Server blocks the 'ad hoc distributed queries'
Statement 'openrowset/OpenDataSource'
Because this component has been disabled as part of the server security configuration. The system administrator can use sp_configure to enable 'ad hoc distributed
Queries '. For more information about enabling 'ad hoc distributed querys', see
"Peripheral application configurator ".
Query relevant information and find the solution:

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

Select *
From OpenDataSource (
'Sqlodb ',
'Data source = servername; user id = sa; Password = Sa'
). Databasename. DBO. Table

------------ Test statement -------------
/**
Exec sp_configure 'show advanced options', 1
Reconfigure
Exec sp_configure 'ad hoc distributed queries ', 1
Reconfigure

Select * from
-- OpenDataSource ('sqlodb', 'Data source = 10.2.13.30; User
Id = trafax; Password = trafax ~! @ # $ '). Alicc. DBO. ed_faxmessage
OpenDataSource ('sqlodb', 'Data source = 10.0.10.28; User
Id = sa; Password = 5162526 '). alict. DBO. ed_faxmessage
Order by comm_id DESC

Update OpenDataSource ('sqlodb', 'Data source = 10.0.10.28; User
Id = sa; Password = 5162526 '). alict. DBO. ed_faxmessage
Set dept = 'ccbu'
Where comm_id = '20140901'

Exec sp_configure 'ad hoc distributed queries ', 0
Reconfigure
Exec sp_configure 'show advanced options', 0
Reconfigure

**/