0

I have about 57k rows that I am trying to insert from MS Access into a SQL Server linked table, but it is very very slow. Can anyone help me with an alternative approach please?

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

Any help is very much appreciated.

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Aravind Ranganathan
  • 25
  • 9
  • Well, what do you expect? Moving 57.000 records from Microsoft Access to SQL server is bound to take some time. We don't have a magic alternate _do the same but faster_ code – Erik A Nov 28 '17 at 15:54
  • You could use ADODB to open a connection to your SQL Server and then do the INSERT using OPENDATASOURCE back to your access database... maybe. [something like this](http://vbcity.com/forums/t/144403.aspx) Otherwise you are going to single-row-insert all 57000 records and it's going to take a while. – JNevill Nov 28 '17 at 15:56
  • @ErikvonAsmuth How do you figure? 57,000 records is not many. The issue here, I believe, is that the insert is being run across the linked server which ends up doing a RBAR insert instead of bulk. edit: JNevill has the right idea. – Jacob H Nov 28 '17 at 15:57
  • The other thought was to import the access table into your SQL Server using SSMS or whatever, then run the insert directly in SQL Server. The real trick is getting a bulk load into your SQL Server for this data. Once you crack that nut, then you are golden. – JNevill Nov 28 '17 at 15:59
  • @JacobH Yes, that indeed is the problem, but avoiding a row-by-row insert while using a running Microsoft Access database on a different machine on the server is hard if not unavoidable (the link by JNevill requires the Access database to be on the same machine or on a shared drive between the SQL server and the machine executing the command). – Erik A Nov 28 '17 at 16:01

1 Answers1

1

The MS Access DoCmd.TransferText method can be placed into a macro. This function can dump a table or query to a text file. It runs pretty quick on a local (source) machine.

Create a batch file calling the bcp utility with the appropriate command line switches to load the SQL Server table. This will run equally fast on the remote (target) SQL Server database. Make sure you check the error log file for batches with errors. You can tune the process with the batch size option.

This will convert your row by agonizing row (RBAR) process into a BULK LOAD process.

Happy Coding

John Miner

The Crafty DBA

CRAFTY DBA
  • 14,351
  • 4
  • 26
  • 30