SQL SERVER shares data conversion methods with Access and Excel _mssql

Source: Internet
Author: User
Tags mdb database sql server query ole
In Transact-SQL statements, we mainly use the OPENDATASOURCE function, the OPENROWSET function, and a detailed description of the function, please refer to the SQL online Help. You can easily implement SQL SERVER, ACCESS, and Excel data transformations using the following methods, which are described in detail:

data Import Export for SQL SERVER and Access

General Data Import Export:

To migrate your Access data to SQL Server using the DTS Wizard, you can use these steps:

1 on the Tools menu in SQL Server Enterprise Manager, select Data transformation
2Services (Data Transformation Services), and then select Czdimport Data (import).
3 Select Microsoft Access as the source in the Choose a Data Source dialog box and type the file name of your. mdb database (. mdb file extension) or browse for the file.
4 in the Choose a destination (select target) dialog box, select Microsoft OLE DB Prov ider for SQL server, select the database server, and then click the necessary authentication method.
5 in Specify Table copy (specify table copy) or query (query) dialog box, click Copy Tables (copy table).
6 in the Select Source Tables dialog box, click Select All (selected). Next, Finish.

Transact-SQL statements for import Export:

1. Query access data in SQL Server:

select*
Fromopendatasource (' Microsoft.Jet.OLEDB.4.0 ',
' Data source= ' C:\DB.mdb '; User id=admin; Password= ') ... Table name

2. Import access into SQL Server

Running in SQL SERVER:
select*
Into newtable
Fromopendatasource (' Microsoft.Jet.OLEDB.4.0 ',
' Data source= ' C:\DB.mdb '; User id=admin; Password= ') ... Table name

3. Insert data from SQL Server tables into an Access table


Running in SQL SERVER:
Insertintoopendatasource (' Microsoft.Jet.OLEDB.4.0 ',
' Data source= ' C:\DB.mdb '; User id=admin; Password= ') ... Table name
(column name 1, column name 2)
Select Column Name 1, column name 2 from SQL table
Instance:
Insertinto OPENROWSET (' microsoft.jet.oledb.4.0 ',
' C:\db.mdb '; ' Admin '; ', Test)
Select Id,name from Test
Insertintoopenrowset (' microsoft.jet.oledb.4.0 ', ' C:\trade.mdb '; ' Admin '; ", table name)
select*
From Sqltablename

second, SQL SERVER and Excel data import Export
1. Query Excel data in SQL Server:


select*
Fromopendatasource (' Microsoft.Jet.OLEDB.4.0 ',
' Data source= ' C:\Book1.xls '; User id=admin; password=; Extended properties=excel 5.0 ') ... [sheet1$]
The following is an example of a query that queries an EXCEL spreadsheet by using the OLE DB provider for Jet.
select*
Fromopendatasource (' Microsoft.Jet.OLEDB.4.0 ',
' Data source= ' C:\Finance\account.xls '; User id=admin; password=; Extended properties=excel 5.0 ') ... xactions


2. Import Excel data into SQL Server:


Select*into newtable
Fromopendatasource (' Microsoft.Jet.OLEDB.4.0 ',
' Data source= ' C:\Book1.xls '; User id=admin; password=; Extended properties=excel 5.0 ') ... [sheet1$]
Instance:
Select*into newtable
Fromopendatasource (' Microsoft.Jet.OLEDB.4.0 ',
' Data source= ' C:\Finance\account.xls '; User id=admin; password=; Extended properties=excel 5.0 ') ... xactions

3, the SQL Server query to the data into an Excel file

T-SQL code:
EXEC Master.. xp_cmdshell ' bcp library name. dbo. Table name out c:\Temp.xls-c-q-s "servername"-U "sa"-P "" "
Parameter: s is SQL Server name; u is user; p is a password
Description: You can also export a variety of formats such as text files
Instance: EXEC Master ... xp_cmdshell ' bcp saletesttmp.dbo.CusAccount out c:\temp1.xls-c-q-s ' pmserver '-u ' sa '-P ' sa '
EXEC Master.. xp_cmdshell ' bcp ' select au_fname, au_lname from pubs. Authors ORDER BY au_lname "Queryout c:\ authors.xls-c-sservername-usa-ppassword"
Applying ADO to export Excel file code in VB6:
Dim cn as New ADODB. Connection
Cn.open "Driver={sql Server}; Server=websvr;database=webmis; Uid=sa; wd=123; "
Cn.execute "Master. xp_cmdshell ' bcp ' select col1, col2 from library name. dbo. Table name "Queryout E:\DT.xls-c-sservername-usa-ppassword '"

4. Insert data into Excel in SQL Server:

Insertintoopendatasource (' Microsoft.Jet.OLEDB.4.0 ',
' Data source= ' C:\Temp.xls '; User id=admin; password=; Extended properties=excel 5.0 ') ... table1 (a1,a2,a3) VALUES (1,2,3)

T-SQL code:
Insertinto
OpenDataSource (' Microsoft.Jet.OLEDB.4.0 ',
' Extended properties=excel 8.0;data source=c:\training\inventur.xls ') ... [filiale1$]
(Bestand, Produkt) VALUES (' Test ')

Summary: Using the above statements, we can easily transform the data in SQL SERVER, Access, and Excel spreadsheet software, providing great convenience for us!
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.