sql数据库导入导出（Import and export SQL database）
Data conversion between SQL SERVER and ACCESS and EXCEL
Familiar with SQL SERVER 2000 database administrators know that their DTS can import and export data, in fact, we can also use Transact-SQL statements for import and export operations. In the Transact-SQL statement, we mainly use the OpenDataSource function, the OPENROWSET function, and the function details, and refer to the SQL online help. Using the following methods, SQL, SERVER, ACCESS, and EXCEL data conversion can be easily implemented, as explained below:
First, import and export data of SQL, SERVER and ACCESS Routine data import and export:
Use the DTS wizard to migrate your Access data to SQL Server, and you can use these steps:
1 on the Tools (tools) menu in the SQL SERVER enterprise manager, select Data Transformation
2Services (data conversion service), and then select czdImport Data (import data).
3 in the Choose a Data Source (select the data source) dialog box, select Microsoft Access as the Source, and then type the name of your.Mdb database (.Mdb file extension) or browse to find the file.
4 in the Choose a Destination (select the target) dialog box,
select Microsoft OLE DB Prov ider for SQL Server, select the database server, and then click the necessary validation methods.
5 in the Specify Table Copy (specify the table copy) or the Query (query) dialog box, click Copy tables (copy table).
6 in the Select Source Tables (select the source table) dialog box, click Select All (all selected). Next, complete.
The Transact-SQL statement is imported and exported:
excel连接sql数据库教程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
-------------------------------------------------------------------------------------------------
2. import access into 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. insert data from the SQL SERVER table into the 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 1, column 2)
Select column 1, column 2, from, SQL table
Example：
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
-------------------------------------------------------------------------------------------------
2。导入将访问SQL Server
——======================================================
在SQL Server里运行：
选择*
为新
从OPENDATASOURCE（'microsoft。飞机。OLEDB。4”，
数据源=“C：\分贝。mdb”；用户ID =管理员；密码=“表名）…
-
----------------------------------------------------------
--------------------------------------
三.将访问SQL Server表里的数据插入到表中
——======================================================
在SQL Server里运行：
插入OPENDATASOURCE（微软。飞机。OLEDB。4”，
数据源=“C：\分贝。mdb”；用户ID =管理员；密码=“表名）…
（列名1，列名2）
选择列名1，列名2从SQL表
实例：
插入OPENROWSET（'microsoft。飞机。OLEDB。4”，
C：\分贝。mdb”；'admin '；'，测试）
从测试中选择id、名称
插入OPENROWSET（'microsoft。飞机。OLEDB。4”，“C：\贸易。MDB '；'管理'；'，表名）
选择*
从sqltablename