Simple summary of SQL Server and Access, Excel data transfer

Source: Internet
Author: User
Tags db2 insert modify
The so-called data transfer in the access|excel|server|sqlserver| is actually referring to SQL Server accessing data between Access and Excel.

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 ~ ~




Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.