SQL Server, access, and Excel Data Transmission Summary

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 a text database, such as acess, Excel, and FoxPro. Currently, after system upgrades and database servers such as sqlserver and Oracle, you often need to access data in the text Database, which leads to such a requirement. 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 does not need to be mentioned, because it is a graphical operation interface and is easy to use.
Here we will mainly talk about the following two examples: Query, add, delete, and modify:
The following nonsense will not say, 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 BCP
Master .. 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 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
For more information about 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, all these content are available in help. I just summarized it for your reference ~~
The above content has been tested ~~
Use the OpenRowSet function to connect to and access remote database data
We sometimes encounter data that needs to be accessed not only on the local server, but also on the remote database server, for example, if we store data with the same table structure on the two servers, what should we do if we combine the two data and display them together? In this case, we use the OpenRowSet function provided by SQL Server, it is easy to use. Remember to reference the OpenRowSet function just like referencing the table name.

We can write as follows:
1. select. * From Table1 a left join OpenRowSet ('msdasql ', 'driver = {SQL Server}; server = 192.168.0.1, 2412; uid = sa; Pwd = BB', dbname. DBO. table2) as B on. id = B. id order by. id DESC
Connect table 2 on SQL Server with port 2412 192.168.0.1 to table 1 on the local server.

2. select * from (select. * From Table1 a union select B. * From OpenRowSet ('msdasql ', 'driver = {SQL Server}; server = 192.168.0.1, 2412; uid = AA; Pwd = BB', dbname. DBO. table2) as B) temptable order by ID DESC
Add the content in table 2 on SQL Server with the address 192.168.0.1 port 2412 to table 1.

In the above example, we use the odbc ole db Provider. Of course, you may also use SQL Server's Microsoft OLE DB, jet's Microsoft OLE DB and other providers to connect.

Use SQL Server's Microsoft OLE DB:
OpenRowSet ('sqlodb', 'server address, port'; 'username'; 'Password', 'SQL statement ')

Use Microsoft ole db of jet:
OpenRowSet ('Microsoft. Jet. oledb.4.0 ', 'c:/test. mdb'; 'admin'; 'pwd', orders)

Note: The preceding two user names and passwords are separated by semicolons.

I just used it today, so I can't write it down if my memory is poor.