再问Access导入SQL

[julong88]

Access里面是正在用的数据，现在要升级到SQL Server，我已经在SQL里把表设计好了（Access和SQL的字段类型略有差异），现在要把数据导入。用
SELECT *
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\DB.mdb";User ID=Admin;Password=')...表名
就提示表已存在

我用SQL的导入向导导入时如果选择第一项“从源数据库复制表和视图”就会报错
如果选择第二个选项就可以“用一条语句指定要传输的数据”是可以的，但这样没效率，总不能每个客户来一次吧
还有就是一次只能导入一个表

请教各位达人有什么好办法？

[julong88]

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

[htl258_Tony]

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

--没有INTO语句怎么会提示“表已存在”


我用SQL的导入向导导入时如果选择第一项“从源数据库复制表和视图”就会报错

--报什么错？

[chuifengde]

如果是一次性升级，那就用access的迁移工具，如果是要多次导数据，那就insert 表(col1,col2....) SELECT *  
FROM OpenDataSource( 'Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\DB.mdb";User ID=Admin;Password=')...表名

[htl258_Tony]

[Quote=引用 1 楼 julong88 的回复:]
SELECT *
INTO newtable
FROM OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0',
'Data Source="c:\DB.mdb";User ID=Admin;Password=' )...表名
[/Quote]

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

[ws_hgo]

/*
openDatasource:不使用连接服务器的名称,而提供特殊的链接信息.
使用openDatasource的前提是仅当 DisallowAdhocAccess 
注册表选项针对指定的提供程序显式设置为 0，
并且启用 Ad Hoc Distributed Queries 高级配置选项时，
OPENDATASOURCE 才可用于访问 OLE DB 数据源中的远程数据。
如果未设置这些选项，则默认行为不允许即席访问。
*/
--代码如下:
exec sp_configure 'show advanced options',1
reconfigure
exec sp_configure 'Ad Hoc Distributed Queries',1
reconfigure
--(1)例如现在我想打开D盘中BJData.mdb中的BJAdmin表的信息
select ID,Title,OwnerName from 
opendataSource('Microsoft.Jet.OLEDB.4.0','Data Source="D:\BJData.mdb";')...BJAdmin
/*Results:
ID          Title                OwnerName
----------- -------------------- --------------------------------------------------
1           xinrong              
4           webmaster            admin
5           bing                 webmaster
(3 行受影响)
*/
--(2)我现在要将BJAdmin中BJNewsCategory中的信息插入到A表中
--先查询下Test.Newscategory
select * from  Test.dbo.Newscategory
/*
ID          Title
----------- --------------------
1           MSSQL
(1 行受影响)
*/
insert into Newscategory select Title from OpenDataSource
('Microsoft.Jet.OLEDB.4.0','Data Source="D:\BJData.mdb";')...BJNewsCategory
--导入之后再查询
select * from  Test.dbo.Newscategory
/*
ID          Title
----------- --------------------
1           MSSQL
2           新闻分类
3           新闻动态
4           工程案例
5           膜结构知识
6           在建工程
(6 行受影响)
*/
--上面的都是在MSSQL中查询Access中的信息,现在将MSSQL中的信息导入到Access中去
--(3)将AdminInfo的信息插入到BJData.mdb中的BJAdmin中去
select * from Test.dbo.AdminInfo
/*
ID          Title
----------- ------------------------------
1           ws_hgo
(1 行受影响)
*/
insert into opendatasource('microsoft.Jet.OLEDB.4.0','Data source="D:/BJData.mdb"')...BJAdmin
(Title,RoleID) select Title,1 from AdminInfo
--然后查询BJAdmin表的信息
select ID,Title,OwnerName from 
opendataSource('Microsoft.Jet.OLEDB.4.0','Data Source="D:\BJData.mdb";')...BJAdmin 
/*
ID          Title                OwnerName
----------- -------------------- --------------------------------------------------
1           xinrong              
4           webmaster            admin
5           bing                 webmaster
12          ws_hgo               NULL
(4 行受影响)
*/
--(4)上面说的是Access与MSSQl之间的操作现在操作下
--Excel与MSSQL之间的操作
--从Excel中读取信息
select * from OpenDataSource
('Microsoft.Jet.OLEDB.4.0','Data Source="D:\work.et";Extended properties=Excel 5.0')...[sheet1$]
/*
EmployeeName        Sex            Age             Post
-----------       -----------    --------      ------------ 
ws_hgo             Boy            23             Senior Programmer
yangzhognyuan      Boy            24             Programmer
chenjing           Girl           24             Designer
(3 行受影响)

--(5)
*/
/*将本地表中的信息导入到远程数据库表中
下面的例子将本地whsxjAdmin种的(Title,Password)插入到远程
zhongyiAdmin中去
*/
insert into opendatasource('SQLOLEDB','Data Source=IP地址;User ID=zhongyi;password=xinrongkeji').sqlzhongyi.dbo.zhongyiAdmin(Title,Password)
select Title,Password from whsxjAdmin
--下面说下OPENROWSET
/*
包含访问 OLE DB 数据源中的远程数据所需的全部连接信息。
当访问链接服务器中的表时，这种方法是一种替代方法，
并且是一种使用 OLE DB 连接并访问远程数据的一次性的临时方法。
*/
--(1)直接查询远程服务器上面的信息
select ID,Title from OpenRowset
('SQLOLEDB','IP地址';'zhongyi';'xinrongkeji','select * from sqlzhongyi.dbo.zhongyiAdmin') t
--(2)查询Access里面的信息
select * from OpenRowset
('microsoft.Jet.OLEDB.4.0','d:\BJData.mdb';'Admin';'',BJAdmin) as g



本文来自CSDN博客，转载请标明出处：http://blog.csdn.net/ws_hgo/archive/2010/04/23/5518918.aspx

[永生天地]

[Quote=引用楼主 julong88 的回复:]
我用SQL的导入向导导入时如果选择第一项“从源数据库复制表和视图”就会报错
[/Quote]

解决这个抱错，你就胜利

[julong88]

IF OBJECT_ID('newtable')>0
DROP TABLE newtable
GO


SQL Server里面的表不能删除啊，是我做过调整的

[julong88]

导入的时候选择查询语句导入，这种语句是可以的

select `RiZhi`.`CreateDt`, `RiZhi`.`Flag`, `RiZhi`.`ID`, `RiZhi`.`MiaoShu`
from `RiZhi`

order by `RiZhi`.`CreateDt`, `RiZhi`.`Flag`, `RiZhi`.`ID`, `RiZhi`.`MiaoShu`

[julong88]

delete from KeHu
SET IDENTITY_INSERT KeHu ON
Insert Into KeHu(ID) Select ID FROM
OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source="C:\111.mdb";User ID=Admin;Password=')...KeHu

没有问题，但很麻烦需要把字段都写上


delete from KeHu
SET IDENTITY_INSERT KeHu ON
Insert Into KeHu Select * FROM
OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source="C:\111.mdb";User ID=Admin;Password=')...KeHu
报错：
仅当使用了列的列表，并且 IDENTITY_INSERT 为 ON 时，才能在表 'KeHu' 中为标识列指定显式值。

delete from KeHu
SET IDENTITY_INSERT KeHu ON
SELECT * Into KeHu FROM
OPENDATASOURCE ('Microsoft.Jet.OLEDB.4.0', 'Data Source="C:\111.mdb";User ID=Admin;Password=')...KeHu
报错：
服务器: 消息 2714，级别 16，状态 6，行 3
数据库中已存在名为 'KeHu' 的对象。