The so-called data transmission is actually referring to data between Access, Excel, SQL Server.
Why do you want to take into account this problem?
Due to historical reasons, customers previous data are in the text database, such as Acss, Excel, FoxPro. Now the system upgrade and database server such as SQL Server, Oracle often need to access data in the text database, so this requires. Projects in front of the previous period of time are a problem with such a problem: data exchange between SQLServer and VFP.
To complete the needs of the title, it is a very simple thing in SQL Server.
Usually there can be 3 ways: 1, DTS Tool 2, BCP 3, Distributed Inquiry
DTS does not need to say, because that is a graphical operation interface, it is easy to get started.
Here mainly talks about two of them, to check, increase, delete, and change as simple examples:
If you have nonsense below, you will be exhibited directly in T-SQL.
First, SQLServer and Access
1. Query the method of data in Access:
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 SQL Server to Access:
INSERT INTO OpenRowSet ('Microsoft.jet.OleDb.4.0', '; Database = C: /Db2.mdb', 'Select * from AcceE table') Select * from sqlserver table or with BCP
MASTER..XP_CMDSHELL'BCP "serv-htjs.dbo.serv_user" OUT "C: /DB3.MDB" -c -q -s "." -u "sa" -p "sa" "
The above difference is mainly: OpenRowSet requires MDB and tables, and BCP will generate the MDB when there is no existence.
3. Write data from Access to SQLSERVER: With the above foundation, this is very simple.
Insert Into Sql Server Table Select * from OpenRowSet ('Microsoft.jet.OleDb.4.0', '; Database = C: /Db2.mdb', 'SELECT * from ACCEE Table')
Or use BCP
MASTER..XP_CMDSHELL'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 = 1sqlserver and Access roughly so much.
Second, SQL Server and Excel
1. Inquiry to 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) HDR = YES can treat XLS's first line of XLS as a field, such as HDR = NO in the first one, will report error 2) [] and Meituan $ must, otherwise m $ can 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 = 'Erquan' Where C LIKE '% f%'
3. Import 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
BCP
MASTER..XP_CMDSHELL'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 $]')
Create if the table serv_user_bak does not exist
For detailed answers to BCP and distributed queries, check the help of SQLServer. SQLSERVER and TXT files, HTML files, VFP files are very easy. . . .
In fact, this content is in the help, even just a summary, convenient for everyone, huh, huh ~~
The above content is tested ~~
