X   Site Message
(Message will auto close in 2 seconds)

Welcome to UtterAccess! Please ( Login   or   Register )

Custom Search
   Reply to this topicStart new topic
> Copy Large Table To SQL, SQL Server 2012    
post Jul 11 2018, 05:58 AM

Posts: 3
Joined: 11-July 18

I have a million record table with a dozen fields that I need to copy to a SQL db that my colleague administers

I have no problem doing this with a much smaller table simply using an Append query, however the larger table bombs out

I have written some very crude code to copy each record at a time AddNew/Update but this only does about 2000 records per minute

Are there any more elegant options that might run a bit quicker?

I promise I did search before posting

Thanks in anticipation
Go to the top of the page
post Jul 11 2018, 07:06 AM

Posts: 891
Joined: 25-April 14

What about doing big chunks,rather than 1 at a time?
Using top 10,000 and a marker field.

Q1= Append top 10000 where marker is null.
Q2= set marker= "x" for top 10000

Then repeat until all are marked.
If 10000 is also too many, try 5000
Go to the top of the page
post Jul 11 2018, 07:26 AM

Access Wiki and Forums Moderator
Posts: 74,503
Joined: 19-June 07
From: SunnySandyEggo


Welcome to UtterAccess!

Just a thought but how about backing up the table on your end and restoring it on their end?
Go to the top of the page
post Jul 11 2018, 08:15 AM

UA Admin
Posts: 34,587
Joined: 20-June 02
From: Newcastle, WA

SQL Server itself has tools designed to do this.

You don't mention, though, what database is used for the SOURCE data. Is it another SQL Server table? An Access table? A table in another database?

I think theDBguy and I are both assuming it's SQL Server --> SQL Server, but please verify.

You can do a backup and restore of the entire database, if that works. Or, if you want to migrate only one table, you can do that in SSMS. This works regardless of the source database (SQL Server, Access, etc.).

There's even a wizard to step you through that.
Attached File  ImportDataStart.png ( 63.94K )Number of downloads: 2

That said, you may have other options, and limitations. For example, I am assuming that you can connect to the SQL Server instance with SSMS. Can you do that?
Go to the top of the page
post Jul 12 2018, 03:47 AM

Posts: 3
Joined: 11-July 18

I should have said that the origin table is in Access2016

I don't have SSMS, although my colleague who administers the target database does

I could suggest that he takes responsibility of importing the table using the wizard as suggested, but I would prefer to copy the data myself

My 'foolproof' record at a time method was left running yesterday but bombed out after 120,000 records with a 'ODBC insert on linked table failed'

Presumably there was a network glitch or something

I think I will try the Batched Append method next, thanks for the suggestion

I should also mention that I will need to do this more than once, so I don't really want to use a 'manual' process

This post has been edited by ukkid35: Jul 12 2018, 03:54 AM
Go to the top of the page
post Jul 13 2018, 06:24 AM

Posts: 3
Joined: 11-July 18

Update on the Batch Append process

1000 records takes 12 seconds to append (setting the markers has no significant overhead)

10000 records takes 120 seconds to append, but seems less reliable

ODBC errors occur frequently, and I haven't attempted to restart the process automatically when they happen

As part of my testing I have had to write a Pass Through query to delete records on the SQL database, it runs instantly

I would like to be able to use a pass through to append local table records on to the SQL table, but my research suggests that is not possible

It looks like the only practical way to get a large table copied is for the SQL administrator to manually import it, not ideal but managable

Many thanks for your help
Go to the top of the page
post Jul 13 2018, 04:02 PM

Posts: 2,836
Joined: 27-February 09

I don't think you can use a pass through to do what you want, because that means it would execute on SQL Server and ignore Access entirely. You need to push the records, not pull them.

You could create a linked table to the SQL Server table, and then loop over the local source table and select a reasonable chunk of records (maybe 10K... you many need to adjust until you find a size that's big enough to be fast but not so big that the records fail to insert. Then you would just select some of the records at a time inside a loop. Failing that, if someone there can do SSIS, you could send them that way and just run it in the middle of the night if you wanted to.

If you had an autoincrementing field in your Access table, you could use that to grab records... something like

-- loop structure goes here
INSERT INTO SQLServerTable (<fieldList>)
SELECT <fieldList>
FROM MyBigTable
WHERE MyAutoIncrField >= x AND MyAutoIncrField<x+10000
x = x + 10000
-- end loop
Go to the top of the page
post Aug 22 2018, 03:34 PM

Posts: 10
Joined: 2-October 15

This is the first part of the process I use. If there is interest I can add the rest of the steps.

The goal is to create a Pass-Through Query Insert Statement with a Values. You use a Select query to pull the data in Access. That modify the Pass-Through Query to contain the data in the Values clause.

The thing does get complicated and has a limit to how much data you can push at once. But I have been able to push
1000 rows in 30 seconds. Trying to do that all with an Access Insert query to a linked SQL Server table would take me six minutes.

1. Create a Pass-Through query that is an SQL Server Insert statement.
2. Create a query in Access that will create the Values part of the Insert statement.
3. In the External Data Ribbon, start to export the second query to a text file.
4. In the steps to do that, click on Advanced.
5. Change the Text Qualifier dropdown to a single quote. SQL Server uses that where Access uses double quotes.
6. Click on Save As to create an Import/Export Specification.
7. You can cancel the export at this point, as the important part was creating the Specification.
8. Run that query in code:
DoCmd.TransferText TransferType:=acExportDelim, _
  SpecificationName:=YourSpecificationName, _
  TableName:=YourQueryName, _

and some more steps...
Go to the top of the page

Custom Search

RSSSearch   Top   Lo-Fi    23rd February 2019 - 03:46 AM