Data conversion between SQL Server and access and Excel

Turn from: Good fun (Baidu space) (I want to summarize it and write it in half. I found there is no depth, or others wrote it well. I will collect it first .)

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 (data conversion service), 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

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

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)

SelectColumn name 1, column name 2FromSQL 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. 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, which is provided by ole db for jet.ProgramQuery an Excel worksheet.

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

3. Export 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" '  
(Study.iader.com)
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' "

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   Into
OpenDataSource ( ' Microsoft. Jet. oledb.4.0 ' ,
' Extended properties = Excel 8.0; Data Source = c: \ training \ inventur.xls ' ) [ Filiale1 $ ]
(Bestand, Produkt) Values ( 20 , ' Test ' )