Simple summary of SQL Server and Access, Excel data transfer

This is a simple summary of SQL Server and Access, Excel data transfer, for your reference learning

The so-called data transmission, in fact, refers to the SQL Server access, Excel information between. Why do we have to consider this problem? For historical reasons, many of the customer's previous data are stored in a text database, such as acess, Excel, and Foxpro. Now the system upgrades and database servers such as SQL Server, Oracle, often need to access the data in the text database, so it will produce this demand. The project that was traveling for a while ago is faced with a problem: The data exchange between SQL Server and VFP.

The need to complete the title is a very simple thing to do in SQL Server. There are usually 3 ways: 1, DTS Tools 2, BCP 3, distributed query DTS No need to say it, because it's a graphical interface and it's easy to get started. This is mainly about the following two, respectively, to check, increase, delete, change as a simple example:

Express in the form of T-SQL directly.

One, SQL Server and Access

1, query access in the data methods:

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 to access from SQL Server:

insert into openrowset('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select * from accee表')
select * from sqlserver表

or with

master..xp_cmdshell'bcp "serv-htjs.dbo.serv_user" out "c:\db3.mdb" -c -q -s"." -u"sa" -p"sa"'

The main difference is that OPENROWSET requires an MDB and table to exist, and BCP generates the MDB when it does not exist

3. Write data to SQL Server from Access:

With the above foundation, this is very simple.

insert into sqlserver表 select * from
openrowset('microsoft.jet.oledb.4.0',';database=c:\db2.mdb','select * from accee表')

or using bcp

master..xp_cmdshell'bcp "serv-htjs.dbo.serv_user" in "c:\db3.mdb" -c -q -s"." -u"sa" -p"sa"'