从Access到SQL Server复制数据 [英] Copying Data from Access to SQL Server
问题描述
我需要一个自动过程将数据从Access表复制到
SQL Server表。速度很重要。什么是推荐的
技术？
我可以从Access导出数据，通过FTP复制，然后导入到
SQL Server。我试过了，速度可以接受。然而，这是一个丑陋的b $ b解决方案，我希望找到一个更好的解决方案 - 最好是与Access RDBMS集成的更好的b $ b解决方案。
我尝试过使用ODBC连接和INSERT查询。这是一个很好的，
清洁技术，但速度极慢（比文件慢大约10倍b / b
复制技术）。
我也尝试过使用OLEDB连接和ADO记录集来从程序上复制数据
。那甚至更慢（比
文件副本慢约30倍）。
这对我来说都没有意义。为什么无法访问传输数据到SQL
服务器通过ODBC或OLEDB的速度几乎和网络一样快？
数据？没有涉及索引或触发器，也没有交易处理 - 只需将一个简单的列到列的数据从一个
表转移到另一个表。我觉得我必须遗漏一些明显的东西。
任何人都可以提供任何见解吗？
-TC
顺便说一句，我是''使用Access 2003和SQL Server 2000.网络是一个
WAN，我正在使用TCP / IP作为默认协议。
I need an automated procedure to copy data from an Access table to a
SQL Server table. Speed is important. What is the recommended
technique?
I can export the data from Access, copy it via FTP, then import it into
SQL Server. I''ve tried that, and the speed is acceptable. It is an ugly
solution, however, and I expect to find a better one -- preferably a
solution better integrated with the Access RDBMS.
I''ve tried using an ODBC connection and an INSERT query. That''s a good,
clean technique, but is extremely slow (about 10x slower than the file
copy technique).
I''ve also tried using an OLEDB connection and ADO recordsets to copy
the data procedurally. That was even slower (about 30x slower than the
file copy).
None of this makes sense to me. Why can''t Access transfer data to SQL
Server through ODBC or OLEDB virtually as fast as the network can carry
the data? There are no indexes or triggers involved, and no transaction
processing -- just a simple column-for-column transfer of data from one
table to another. I feel as though I must be missing something obvious.
Can anyone offer any insight?
-TC
By the way, I''m using Access 2003 and SQL Server 2000. The network is a
WAN and I''m using TCP/IP as the default protocol.
推荐答案
有很多方法可以实现你想要的东西，下面的列表并不是很好，他们恰好是我做类似工作的方式。 />
您可以从Access推送数据或从SQL Server中提取数据。
推送数据
1 ）Recordset到Recordset
逐行复制数据从一个记录集到字段再按
另一个
2）Recordset to Insert查询
为记录集中的每一行创建一个Insert语句
3）链接表插入
链接到SQL Server表并使用Insert语句插入
数据
拉数据
1）DTS包
在SQL中创建一个DTS包来拉取数据，你可以从中执行
DTS包Access数据库或通过在执行DTS包的SQL服务器上调用存储过程
。
2）链接服务器
您可以创建SQL中的链接服务器指向您的Access数据库
然后您可以调用存储过程将数据从链接的
服务器插入到SQL表中。
-
Terry Kreft
" TC" < go ********* @ yahoo.comwrote in message
news：11 ********************* *@m73g2000cwd.googlegr oups.com ...
There are many ways to achieve what you want, the following list is not
exhaustive they just happen to be the ways I have done similar work.
You can either push the data from Access or pull the data from SQL Server.
Pushing the data
1) Recordset to Recordset
Copy the data row by row and field by field from one recordset to
the other
2) Recordset to Insert query
Create an Insert statement for each row in the recordset
3) Linked table insert
Link to the SQL Server table and use an Insert statement to insert
the data
Pulling the data
1) DTS package
Create a DTS package in SQL to pull the data, you can execute the
DTS package either from the Access database or by calling a stored procedure
on the SQL server which executes the DTS package.
2) Linked Server
You can create a linked server in SQL which points at your Access DB
you can then call a stored procedure to insert the data from the linked
server into your SQL table.
--
Terry Kreft
"TC" <go*********@yahoo.comwrote in message
news:11**********************@m73g2000cwd.googlegr oups.com...
我需要一个自动程序将数据从Access表复制到
SQL服务器表。速度很重要。什么是推荐的
技术？
我可以从Access导出数据，通过FTP复制，然后导入到
SQL Server。我试过了，速度可以接受。然而，这是一个丑陋的b $ b解决方案，我希望找到一个更好的解决方案 - 最好是与Access RDBMS集成的更好的b $ b解决方案。
我尝试过使用ODBC连接和INSERT查询。这是一个很好的，
清洁技术，但速度极慢（比文件慢大约10倍b / b
复制技术）。
我也尝试过使用OLEDB连接和ADO记录集来从程序上复制数据
。那甚至更慢（比
文件副本慢约30倍）。
这对我来说都没有意义。为什么无法访问传输数据到SQL
服务器通过ODBC或OLEDB的速度几乎和网络一样快？
数据？没有涉及索引或触发器，也没有交易处理 - 只需将一个简单的列到列的数据从一个
表转移到另一个表。我觉得好像我必须遗漏一些明显的东西。
任何人都可以提供任何见解吗？
-TC
顺便说一句，我正在使用Access 2003和SQL Server 2000.网络是一个
WAN，我正在使用TCP / IP作为默认协议。
I need an automated procedure to copy data from an Access table to a
SQL Server table. Speed is important. What is the recommended
technique?
I can export the data from Access, copy it via FTP, then import it into
SQL Server. I''ve tried that, and the speed is acceptable. It is an ugly
solution, however, and I expect to find a better one -- preferably a
solution better integrated with the Access RDBMS.
I''ve tried using an ODBC connection and an INSERT query. That''s a good,
clean technique, but is extremely slow (about 10x slower than the file
copy technique).
I''ve also tried using an OLEDB connection and ADO recordsets to copy
the data procedurally. That was even slower (about 30x slower than the
file copy).
None of this makes sense to me. Why can''t Access transfer data to SQL
Server through ODBC or OLEDB virtually as fast as the network can carry
the data? There are no indexes or triggers involved, and no transaction
processing -- just a simple column-for-column transfer of data from one
table to another. I feel as though I must be missing something obvious.
Can anyone offer any insight?
-TC
By the way, I''m using Access 2003 and SQL Server 2000. The network is a
WAN and I''m using TCP/IP as the default protocol.
TC写的消息
< 11 ****************** ****@m73g2000cwd.googlegroups .com：
TC wrote in message
<11**********************@m73g2000cwd.googlegroups .com:
我需要一个自动程序将数据从Access表复制到
SQL服务器表。速度很重要。什么是推荐的
技术？
我可以从Access导出数据，通过FTP复制，然后导入它
进入SQL Server。我试过了，速度可以接受。然而，这是一个丑陋的解决方案，我希望找到一个更好的解决方案 -
，最好是与Access RDBMS更好地集成的解决方案。
我尝试过使用ODBC连接和INSERT查询。那是一个很好的，干净的技术，但速度非常慢（比文件复制技术的价格慢了大约10倍）。
我也尝试过使用OLEDB连接和ADO记录集来从程序上复制数据
。那甚至更慢（比文件副本的b / b低约30倍）。
这对我来说都没有意义。为什么无法访问传输数据到SQL
服务器通过ODBC或OLEDB几乎和网络一样快？
携带数据？没有涉及索引或触发器，也没有
事务处理 - 只需从一个表到另一个表的简单列到列传输
数据。我觉得好像一定要错过
显而易见的东西。任何人都可以提供任何见解吗？
-TC
顺便说一下，我正在使用Access 2003和SQL Server 2000.网络
a WAN我正在使用TCP / IP作为默认协议。
I need an automated procedure to copy data from an Access table to a
SQL Server table. Speed is important. What is the recommended
technique?
I can export the data from Access, copy it via FTP, then import it
into SQL Server. I''ve tried that, and the speed is acceptable. It is
an ugly solution, however, and I expect to find a better one --
preferably a solution better integrated with the Access RDBMS.
I''ve tried using an ODBC connection and an INSERT query. That''s a
good, clean technique, but is extremely slow (about 10x slower than
the file copy technique).
I''ve also tried using an OLEDB connection and ADO recordsets to copy
the data procedurally. That was even slower (about 30x slower than
the file copy).
None of this makes sense to me. Why can''t Access transfer data to SQL
Server through ODBC or OLEDB virtually as fast as the network can
carry the data? There are no indexes or triggers involved, and no
transaction processing -- just a simple column-for-column transfer of
data from one table to another. I feel as though I must be missing
something obvious. Can anyone offer any insight?
-TC
By the way, I''m using Access 2003 and SQL Server 2000. The network is
a WAN and I''m using TCP/IP as the default protocol.
您是否尝试过Openrowset（适用于换行符）？
http://msdn.microsoft.com/library/de...oa-oz_78z8.asp
说一个字段/一个表格，创建一个SP
CREATE PROCEDURE dbo.uspCopyTable
AS
INSERT INTO
dbo.mytable（myfield）
SELECT
myfield
来自OPENROWSET（''Microsoft.Jet.OLEDB.4.0''，
''c：\ mymathath \mydb.mdb''; ''admin'';''mypwd''，mytable）
GO
用这样的东西把它烧掉
dim cn as adodb.connection
set cn = new adodb.connection
cn.open" Provider = sqloledb;" &安培; _
" Data Source = myServerName;" &安培; _
" Initial Catalog = myDatabaseName;" &安培; _
Integrated Security = SSPI
cn.execute" dbo.uspCopyTable"
-
Roy-Vidar
Have you tried Openrowset (whatch for linebreaks)?
http://msdn.microsoft.com/library/de...oa-oz_78z8.asp
say a one field/one table thingie, create an SP
CREATE PROCEDURE dbo.uspCopyTable
AS
INSERT INTO
dbo.mytable (myfield)
SELECT
myfield
FROM OPENROWSET(''Microsoft.Jet.OLEDB.4.0'',
''c:\mypath\mydb.mdb'';''admin'';''mypwd'', mytable)
GO
fire it off by something like this
dim cn as adodb.connection
set cn = new adodb.connection
cn.open "Provider=sqloledb;" & _
"Data Source=myServerName;" & _
"Initial Catalog=myDatabaseName;" & _
"Integrated Security=SSPI"
cn.execute "dbo.uspCopyTable"
--
Roy-Vidar
如果我理解你的要求：
我创建一个连接到的ADP文件SQL Server / SQL数据库。
转移表Temp我从MDB文件运行此代码：
****
Const ADPFile As String =" C：\Documents and Settings \Lyle Fairfield\My
Documents\Access\FFDBABooks.adp"
Sub temp（）
DoCmd.TransferDatabase acExport ，Microsoft Access，_
ADPFile，acTable，Temp，Temp，False
End Sub
* ***
我无法对速度发表评论，因为我只将它用于小桌子，
但对他们而言似乎是即时的。
在我的完整代码中，我遍历表名并将它们全部传输。 （我的
实际使用情况正好相反;我使用它来保存我的远程SQL Server数据本地机器上的工作MDB
备份。）
当然，列定义是为了遵守SQL Server
认为它所得到的。
索引是不包括在转移中。
转移后，ADP完全可自动化。当然。如果我需要
附加我补充我的代码在ADP中运行SQL来执行追加和
来删除临时表，或者我可以运行SQL来修改列类型
和添加索引。
If I understand what you require correctly:
I create an ADP file connected to the SQL Server / SQL Database.
To transfer Table "Temp" I run this code from the MDB file:
****
Const ADPFile As String = "C:\Documents and Settings\Lyle Fairfield\My
Documents\Access\FFDBABooks.adp"
Sub temp()
DoCmd.TransferDatabase acExport, "Microsoft Access", _
ADPFile, acTable, "Temp", "Temp", False
End Sub
****
I cannot comment about speed as I have used this only for small tables,
but it seems instantaneous for them.
In my full code I loop through Table Names and transfer them all. (My
actual use for this is in reverse; I use it to keep a working MDB
backup on my local machne of my remote SQL Server Data).
Column Defintions are munged, of course, to comply with what SQL Server
thinks it is getting.
Indexes are not included in the Transfer.
After the Transfer the ADP is fully "automatable" of course. If I need
Append I supplement my code to run SQL in the ADP to do the Append and
to delete the Temporary Table, or I can run SQL to modify Column Types
and to Add Indexes.
这篇关于从Access到SQL Server复制数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
