SQL Server data conversion with Access, Excel

Database administrators familiar with SQL SERVER 2000 know that their DTS can import exports of data, but we can also use Transact-SQL statements for import and export operations. 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

2.Services (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 All). Next, Finish.