Data conversion between SQL SERVER, 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 statements to import and export data. In a Transact-SQL statement, we mainly use the OpenDataSource function and OPENROWSET function. For more information about functions, see SQL online help. You can easily convert SQL SERVER, ACCESS, and EXCEL data by using the following methods:

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

Bytes -------------------------------------------------------------------------------------------------

1 2 3 Next Page

[Content navigation]
Page 1st: import and export data of SQL SERVER and ACCESS	Page 2nd: import and export data of SQL SERVER and ACCESS
Page 3rd: SQL SERVER and EXCEL Data Import and Export