Fuze Simple summary of SQL Server and Access, Excel data transfer

access|excel|server|sqlserver| data
Http://www.tongyi.net/article/20031101/200311013786.shtml

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 to do it: 1, DTS Tools 2, BCP 3, distributed queries

DTS does not need to say, because it is a graphical interface, it is easy to get started.

This is mainly about the following two, respectively, to check, increase, delete, change as a simple example:

The following nonsense is not said, directly in the form of T-SQL expression.


One, SQL Server and Access

1, query access in the data methods:

SELECT * from OpenRowSet (' microsoft.jet.oledb.4.0 ', ';d atabase=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 ', ';d atabase=c:\db2.mdb ', ' SELECT * from Accee table ')
SELECT * From SQL Server table
or using bcp

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 from Access to SQL Server: With the above foundation, this is easy.

INSERT INTO SQL Server table select * FROM
OpenRowSet (' microsoft.jet.oledb.4.0 ', ';d atabase=c:\db2.mdb ', ' SELECT * from Accee table ')

or using 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 ', ';d atabase=c:\db2.mdb ', ' select * from Serv_user ')
where lock=0

5. Modify Access data:

Update OpenRowSet (' microsoft.jet.oledb.4.0 ', ';d atabase=c:\db2.mdb ', ' select * from Serv_user ')
Set Lock=1

SQL Server and access are roughly that much.


Second, SQL Server and Excel


1, query 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 when the Hdr=yes can be the 1th row of XLS as a field, such as the 1th of the hdr=no words, where will be an error
2) [] and USD $ must be, 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

or 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 SQL Server:

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 detailed answers to BCP and distributed queries, check out the help from SQL Server.
SQL Server and TXT file, HTML file, VFP file data exchange all seem very easy ....

In fact, these content in the help have, I just summed up a bit, convenient for everyone to reference, hehe ~ ~

The above content has been tested ~ ~