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 (Data conversion service), and then select czdImport Data (import Data ).
○ 3 select Microsoft Access as the Source in the Choose a Data Source dialog box, and then type 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 database Server, and click the required authentication method.
○ 5. In the Specify Table Copy (specifying Table Copy) or Query dialog box, click Copy tables ).
○ 6 in the Select Source Tables dialog box, click Select All ). Next, complete.
Use a Transact-SQL statement to import and export data:
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
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
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
Ii. Import and export data from SQL SERVER and EXCEL
1. 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. It queries an Excel spreadsheet through the ole db provider for Jet.
SELECT * FROM OpenDataSource ('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 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
3. Export the data queried in SQL SERVER into an Excel file
T-SQL code:
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.exe cute "master .. xp_mongoshell 'bcp" SELECT col1, col2 FROM database name. dbo. Table Name "queryout E: \ DT.xls-c-Sservername-Usa-ppassword '"
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 ')
Summary: with the above statements, we can easily convert data in SQL SERVER, ACCESS, and EXCEL spreadsheet software, which provides us with great convenience!
2. Import access to SQL server
SELECT question. qst_no AS Expr1, question. qst_asw AS Expr2, Rowset_1.rst_asw AS Expr3
FROM question inner join opendatasource ('Microsoft. Jet. OLEDB.4.0 ',
'Data Source = "c: \ DB. mdb"; User ID = Admin; Password = ')... test Rowset_1 ON question. qst_no = Rowset_1.qst_no order by question. qst_no
SELECT question. qst_no AS Expr1, question. qst_asw AS Expr2, Rowset_1.rst_asw AS Expr3
FROM question inner join opendatasource ('Microsoft. Jet. OLEDB.4.0 ',
'Data Source = App. Path & "\ DB. mdb"; User ID = Admin; Password = ')... test Rowset_1 ON
Question. qst_no = Rowset_1.qst_no order by question. qst_no
Yle = "text-indent: 2em;"> (Note: With office2007, you can set the 'Microsoft. jet. OLEDB.4.0 'is changed to 'Microsoft. ACE. OLEDB.12.0 ', the provisioner between 2003 and 2007 is different! In this special statement ~~ Otherwise, the "Microsoft. ACE. OLEDB.12.0 'provider is not registered locally .." !!)