SQL Server, Access, and Excel Data Transmission Summary

The so-called data transmission actually refers to the Access between SQL Server and Excel. Why should we take this issue into consideration? Due to historical reasons, many of the customer's previous data is stored in the textDatabase, Such as Acess, Excel, and Foxpro. Current system upgrades andDatabaseServerFor example, after SQL Server and ORACLE, you often need to access textDatabaseData, so this requirement is generated. A project that has been on a business trip for some time ago is faced with the following problem: data exchange between SQLServer and VFP.

To complete the title, it is very simple in SQLServer. Generally, there are three methods: 1. DTS tool 2, BCP 3, and distributed query DTS. It is easy to use because it is a graphical operation interface. Here we will mainly talk about the following two examples: Query, add, delete, and modify:

Directly in the form of T-SQL.

I. SQLServer and Access

1. How to query Access data:

Select * from OpenRowSet ('Microsoft. jet. oledb.4.0 ','; database = c: \ db2.mdb ', 'select * from serv_user ')

Or select * from OpenDataSource ('Microsoft. Jet. OLEDB.4.0 ', 'Data Source = "c: \ DB2.mdb"; User ID = Admin; Password =')... serv_user

2. Write Data from SQLServer to Access:

Insert into OpenRowSet ('Microsoft. jet. oledb.4.0 ','; database = c: \ db2.mdb ', 'select * from Accee table ')
Select * from SQLServer table

Or use BCPmaster .. xp_mongoshell 'bcp "serv-htjs.dbo.serv_user" out "c: \ db3.mdb"-c-q-S "."-U "sa"-P "sa "'

The main difference above is: OpenRowSet requires the existence of mdb and tables. BCP will generate the mdb when it does not exist.

3. Write Data from Access to SQLServer:

With the above foundation, this is very simple.

Insert into SQLServer table select * from
OpenRowSet ('Microsoft. jet. oledb.4.0 ','; database = c: \ db2.mdb ', 'select * from Accee table ')

Or use BCP

Master .. xp_mongoshell 'bcp "serv-htjs.dbo.serv_user" in "c: \ db3.mdb"-c-q-S "."-U "sa"-P "sa "'

4. delete Access data:

Delete from OpenRowSet ('Microsoft. jet. oledb.4.0 ','; database = c: \ db2.mdb ', 'select * from serv_user ')
Where lock = 0

5. Modify Access data:

Update OpenRowSet ('Microsoft. jet. oledb.4.0 ','; database = c: \ db2.mdb ', 'select * from serv_user ')
Set lock = 1

SQL Server and Access are roughly the same.

Ii. SQLServer and Excel

1. query from Excel

Select * from OpenRowSet ('Microsoft. jet. oledb.4.0 ', 'excel 8.0; HDR = yes; database = c: \ book1.xls;', 'select * from [Sheet1 $] ') where c like' % f %'

Select * from
OPENROWSET ('Microsoft. JET. OLEDB.4.0'
, 'Excel 5.0; HDR = YES; IMEX = 2; DATABASE = c: \ book1.xls ', [sheet1 $])

1) When hdr = yes, you can view the 1st rows of xls as fields. For example, if hdr = no is set to 1st, an error will be reported during where.
2) [] and Meiyuan $ are required; otherwise, M $ may not recognize this account.

2. Modify Execl

Update OpenRowSet ('Microsoft. jet. oledb.4.0 ', 'excel 8.0; hdr = yes; database = c: \ book1.xls;', 'select * from [Sheet1 $] ')
Set a = 'erqua' where c like '% f %'

3. Import and Export
Insert into OpenRowSet ('Microsoft. jet. oledb.4.0 ', 'excel 8.0; hdr = yes; database = c: \ book1.xls;', 'select * from [Sheet2 $] ') (id, name)
Select id, name from serv_user

Or BCP master .. xp_mongoshell 'bcp "serv-htjs.dbo.serv_user" out "c: \ book2.xls"-c-q-S "."-U "sa"-P "sa "'

Import from Excel to SQLServer:

Select * into serv_user_bak
From OpenRowSet ('Microsoft. jet. oledb.4.0 ', 'excel 8.0; HDR = yes; database = c: \ book1.xls;', 'select * from [Sheet1 $] ')

If the table serv_user_bak does not exist, create detailed answers for BCP and distributed queries. Check the help provided by SQLServer. Data exchange between SQLServer and txt files, HTML files, and VFP files is very easy.

In fact, these contents are provided in the help documentation for your reference. The above content has been tested.