Conversion between different databases

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

Transact-SQL statements for import Export:

1. Query access data in SQL Server:

-- ================================================

SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\DB.mdb";User ID=Admin;Password=')...表名

----------------------------------------------------------------------------------------

2. Import access into SQL Server

-- ================================================

Running in SQL SERVER:

SELECT *
INTO newtable
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\DB.mdb";User ID=Admin;Password=' )...表名

-----------------------------------------------------------------------------------

3. Insert data from SQL Server tables into an Access table

-- ==============================================

Running in SQL SERVER:

insert into OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source=" c:\DB.mdb";User ID=Admin;Password=')...表名

(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'; '', 表名)
SELECT *
FROM sqltablename

------------------------------------------------------------------