通过访问需要很长时间，将记录插入SQL Server链接表中

Question

我试图从MS访问中插入SQL Server链接表中的大约57K行，但非常慢。谁能用另一种方法来帮助我？

task0 = "INSERT INTO dbo_WorkTable (ID, [Batch ID], [Pay Group], [Pay Group Description], [General Ledger Account], [General Ledger Cost Center], [General Ledger Department], [Work Center], [Pay Period Ending Date], Hours, Amount, Week, [Pay Type Code], [Pay Type Description], [File Number], Name, [HOURLY SALARY], [FULL TIME_PART TIME], ACTIVE_INACTIVE, [HOURLY RATE] )" _
& "SELECT WorkTable.ID, WorkTable.[L Batch ID], WorkTable.[Pay Group], WorkTable.[Pay Group Description], WorkTable.[General Ledger Account], WorkTable.[General Ledger Cost Center], WorkTable.[General Ledger Department], WorkTable.[Work Center], WorkTable.[Pay Period Ending Date], WorkTable.Hours, WorkTable.Amount, WorkTable.Week, WorkTable.[Pay Type Code], WorkTable.[Pay Type Description], WorkTable.[File Number], WorkTable.Name, WorkTable.[HOURLY SALARY], WorkTable.[FULL TIME_PART TIME], WorkTable.ACTIVE_INACTIVE, WorkTable.[HOURLY RATE]" _
& " FROM WorkTable; "
CurrentDb.Execute(task0), dbFailOnError

任何帮助都非常感谢。

Answer 1

可以将MS Access Docmd.transfertext方法放入宏中。此功能可以将表或查询到文本文件。它在本地（源）机上很快运行。

创建一个带有适当命令行开关的BCP实用程序的批处理文件，以加载SQL Server表。这将在远程（目标）SQL Server数据库上同样快速运行。确保检查错误日志文件中是否有错误。您可以使用批处理大小选项来调整该过程。

这将通过苦难行（RBAR）过程转换为批量加载过程。

快乐的编码

John Miner

狡猾的DBA