Database Administrators familiar with SQLSERVER2000 know that DTS can import and export data. In fact, we can also use Transact-SQL statements to import and export data. In the Transact-SQL statement, we mainly use the OpenDataSource function and OPENROWSET function. For details about the function, refer to the SQL online help.
Database Administrators familiar with SQL server 2000 know that DTS can import and export data. In fact, we can also use Transact-SQL statements to import and export data. In the Transact-SQL statement, we mainly use the OpenDataSource function and OPENROWSET function. For details about the function, refer to the SQL online help.
Familiar with SQL SERVER 2000
DataDatabase Administrators know that their data transmission can be performed
DataIn fact, we can also use the Transact-SQL statement to import and export data. In the Transact-SQL statement, we mainly use the OpenDataSource function and OPENROWSET function. For details about the function, refer to the SQL online help. The following methods can be used to easily implement SQL SERVER, access, and Excel
Data
Conversion, The details are described as follows:
I. SQL SERVER and ACCESS
DataImport and Export
Regular
DataImport and export:
Use the DTS wizard to migrate your Access
DataTo SQL Server, you can use these steps:
○ 1. On the Tools menu in the SQL SERVER Enterprise governance tool, select Data Transformation
○ 2 Services (
Data
ConversionService), and then select czdImport Data (Import
Data).
○ 3 in Choose a Data Source (select
DataSource) dialog box, select Microsoft Access as the Source, and then type your. mdb
DataFile Name of the Library (. 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, and select
DataDatabase server, and then 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 in SQL SERVER
Data:
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. Set
DataInsert to 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. SQL SERVER and EXCEL
DataImport and Export
1. query Excel in SQL SERVER
Data:
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. Convert
DataImport 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. query
DataExport 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
Data:
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
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
DataProceed
Conversion, Providing us with great convenience!
Refer:
Http://www.itrain.de/knowhow/ SQL /transfer/adhoc/
