SQL Server, Excel, and access data import

Source: Internet
Author: User
Tags mdb database

 

 

  Data conversion between SQL Server and access and Excel

Database Administrators familiar with SQL Server 2000 know that DTS can import and export data. In fact, we can also use TransAct - SQL statement. In - In SQL statements, we mainly use the OpenDataSource function,   OpenRowSet For more information about functions, see SQL online help. The following methods can be easily implemented: SQL Server, access, Excel Data conversion, detailed descriptions are as follows:

I. Import and export data from SQL Server and access

General data import and export:

Use the DTS wizard to migrate your access data to SQL Server. You can use these steps:

1. On the Tools menu in SQL Server Enterprise Manager, select data transformation
2 services, and then select czdimport data (import data ).
3. In the choose a data source dialog box, select Microsoft Access As The source, Then, type the file name of Your. mdb database (. MDB File Extension) or browse to find 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 required authentication method .
5 In specify Table In the copy or query dialog box, click Copy tables ).
6. In the select Source tables dialog box, click Select All (All selected ). Next, complete.

TransAct - Import and Export SQL statements:

1 . Query access data in SQL Server:
-- ========================================================== ====================
Select *
From OpenDataSource ( ' Microsoft. Jet. oledb.4.0 ' ,
' Data Source = "C: \ dB. mdb"; user id = admin; Password = ' )... Table name
-- Bytes -----------------------------------------------------------------------------------------------
2 . Import access to SQL Server
-- ========================================================== ====================
Run in SQL Server:
Select *
Into Newtable
From OpenDataSource ( ' Microsoft. Jet. oledb.4.0 ' ,
' Data Source = "C: \ dB. mdb"; user id = admin; Password = ' )... Table name
-- Bytes -----------------------------------------------------------------------------------------------
3 . Insert data in the SQL Server table to the access table.
-- ========================================================== ====================
Run in SQL Server:
Insert Into OpenDataSource ( ' 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:
Insert Into OpenRowSet ( ' Microsoft. Jet. oledb.4.0 ' ,
' C: \ dB. MDB ' ; ' Admin ' ; '' , Test)
Select ID, name From Test
Insert Into OpenRowSet ( ' Microsoft. Jet. oledb.4.0 ' , ' C: \ trade. MDB ' ; ' Admin ' ; '' , Table name)
Select *
From Sqltablename
-- Bytes -----------------------------------------------------------------------------------------------

Ii. Import and export data from SQL Server and Excel
1 To query Excel Data in SQL Server:
-- ========================================================== ====================
Select *
From OpenDataSource ( ' 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, which is provided by ole db for jet.ProgramQuery an Excel worksheet.
Select *
From OpenDataSource ( ' Microsoft. Jet. oledb.4.0 ' ,
' Data Source = "C: \ finance \ account.xls"; user id = admin; Password =; extended properties = Excel 5.0 ' )... Xactions
-- Bytes -----------------------------------------------------------------------------------------------

2 Import Excel Data to SQL Server:
-- ========================================================== ====================
Select * Into Newtable
From OpenDataSource ( ' Microsoft. Jet. oledb.4.0 ' ,
' Data Source = "C: \ book1.xls"; user id = admin; Password =; extended properties = Excel 5.0 ' )... [ Sheet1 $ ]
Instance:
Select * Into Newtable
From OpenDataSource ( ' Microsoft. Jet. oledb.4.0 ' ,
' Data Source = "C: \ finance \ account.xls"; user id = admin; Password =; extended properties = Excel 5.0 ' )... Xactions
-- Bytes -----------------------------------------------------------------------------------------------

3 Export the data queried in SQL Server into an Excel file
-- ========================================================== ====================
T - SQLCode:
Exec Master .. xp_mongoshell ' BCP database name. DBO. Table name out c: \ temp.xls-C-Q-s "servername"-U "sa"-P "" '
Parameter: s indicates the SQL server name, u indicates the user, and P indicates the password.
Note: You can also export text files and other formats.
Instance: Exec Master .. xp_mongoshell ' BCP saletesttmp. DBO. cusaccount out c: \ temp1.xls-C-Q-s "pmserver"-U "sa"-P "sa" '
Exec Master .. xp_mongoshell ' BCP "select au_fname, au_lname from pubs.. Authors order by au_lname" queryout c: \ authors.xls-C-sservername-USA-ppassword '
Use ADO to export the 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_mongoshell ' BCP "select col1, col2 from database name. DBO. Table Name" queryout E: \ dt.xls-C-sservername-USA-ppassword ' "
-- Bytes ----------------------------------------------------------------------------------------------

4 Insert data to excel in SQL Server:
-- ========================================================== ====================
Insert Into OpenDataSource ( ' 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:
Insert Into
OpenDataSource ( ' Microsoft. Jet. oledb.4.0 ' ,
' Extended properties = Excel 8.0; Data Source = c: \ training \ inventur.xls ' )... [ Filiale1 $ ]
(Bestand, Produkt) Values ( 20 , ' Test ' )
-- Bytes -----------------------------------------------------------------------------------------------

Summary: with the above statements, we can easily convert data in SQL Server, access, and Excel spreadsheet software, which provides us with great convenience!

 

 

 

 

 

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.