Data Import between access and sqlserver2000

SQL statement execution using SQL Server's query Analyzer: 1. Import access data to SQL Server

Run the following statement in sqlserver to import data from Table b04 in access to the sqltablenaem table in sqlserver.

Insert into sqltablename (sfield1, sfield2, sfield3, sfield4, sfield5, sfield6)

Select convert (char (10), afield1), afield2, afield3, afield4, afield5, afield6

From OpenDataSource ('Microsoft. Jet. oledb.4.0 ', 'Data source = "C: \ doc \ accessdb. mdb"; Jet oledb: Database Password = ABC)... b04

Note:

1. Convert (char (10), afield1) can convert data types and control the length;

2. in "OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "C: \ doc \ accessdb. mdb "; Jet oledb: Database Password = ABC )... data Source = "C: \ doc \ accessdb in b04. MDB is the physical path of the Access Database (ensure that the two databases are on one machine );

3. In Password = ABC, "ABC" is the password of the Access database. If the database has no password, it is left blank;

4. b04 indicates the name of the table in the ACCESS database;

5. You can also add conditions such as where afield = 'A' and... to import data with selection conditions.

2. Import SQL server data to access

Run the following command in sqlserver query Analyzer:Code

Insert into OpenDataSource ('Microsoft. jet. oledb.4.0 ', 'Data source = "C: \ doc \ accessdb. mdb "; Jet oledb: Database Password = ABC )... b04 (afield1, afield2, afield3, afield4, afield5, afield6)

Select sfield1, sfield2, sfield3, sfield4, sfield5, sfield6

From sqltablename

Note: You can also add conditions such as where afield = 'A' and... to import data with selection conditions.

Summary: these are all insert statements,ArticleOnly one form of insert is used, and Other forms can be used for data mutual import.