Shulou(Shulou.com)05/31 Report--

This article mainly talks about "how to realize the data conversion between SQL SERVER and ACCESS, EXCEL". Interested friends may wish to have a look. The method introduced in this paper is simple, fast and practical. Let's let the editor take you to learn how to convert SQL SERVER to ACCESS and EXCEL.

Database administrators who are familiar with SQL SERVER 2000 all know that their DTS can import and export data. in fact, we can also use Transact-SQL statements for import and export operations. In the Transact-SQL statement, we mainly use OpenDataSource function and OPENROWSET function. For a detailed description of the function, please refer to the SQL online help. SQL SERVER, ACCESS and EXCEL data conversion can be easily realized by using the following methods, which are described in detail as follows:

I. data import and export of SQL SERVER and ACCESS

General data import and export:

Using the DTS wizard to migrate your Access data to SQL Server, you can use these steps:

○ 1 in SQL SERVER Enterprise Manager, on the Tools menu, select Data Transformation

○ 2Services (data conversion service), and then select czdImport Data (Import data).

○ 3 Select Microsoft Access as the Source in the Choose a Data Source (Select data Source) dialog box, and then type the file name of your .mdb database (.mdb file extension) or browse for the file.

○ 4 in the Choose a Destination dialog box, select Microsoft OLE DB Prov ider for SQL Server, select the database server, and then click the necessary authentication method.

○ 5 Click Copy tables (copy Table) in the Specify Table Copy (specify Table copy) or Query (query) dialog box.

○ 6 in the Select Source Tables (Select Source Table) dialog box, click Select All (all selected). Next step, done.

Transact-SQL statement for import and export:

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

two。 Import access into 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

3. Insert data from SQL SERVER table into 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

Example:

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. Data import and export of 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 that queries the 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. Import the data of Excel into 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 $]

Example:

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. Import the data queried in SQL SERVER into an Excel file

T-SQL Code:

EXEC master..xp_cmdshell 'bcp library name. Dbo. Table name out c:\ Temp.xls-c-Q-S "servername"-U "sa"-P ""'

Parameter: s is the SQL server name; U is the user; P is the password

Description: you can also export text files and other formats

Example: EXEC master..xp_cmdshell 'bcp saletesttmp.dbo.CusAccount out c:\ temp1.xls-c-Q-S "pmserver"-U "sa"-P "sa"'

EXEC master..xp_cmdshell 'bcp "Select au_fname, au_lname FROM pubs..authors orDER BY au_lname" queryout C:\ authors.xls-c-Sservername-Usa-Ppassword'

Apply 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.execute "master..xp_cmdshell 'bcp" Select col1, col2 FROM library name. Dbo. Table name "queryout E:\ DT.xls-c-Sservername-Usa-Ppassword'"

4. Insert data into 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 (A1MagneA2memaA3) values (1Main2meme 3)

T-SQL Code:

Insert INTO

OPENDATASOURCE ('Microsoft.JET.OLEDB.4.0','Extended Properties=Excel 8.0 X data source=C:\ training\ inventur.xls')... [Filiale1 $] (bestand, produkt) VALUES (20,' Test')

At this point, I believe you have a deeper understanding of "how to realize the data conversion between SQL SERVER and ACCESS, EXCEL". You might as well do it in practice. Here is the website, more related content can enter the relevant channels to inquire, follow us, continue to learn!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.