UtterAccess.com
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    
 
   
ukkid35
post Jul 11 2018, 05:58 AM
Post#1



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
 
ranman256
post Jul 11 2018, 07:06 AM
Post#2



Posts: 867
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
 
theDBguy
post Jul 11 2018, 07:26 AM
Post#3


Access Wiki and Forums Moderator
Posts: 72,769
Joined: 19-June 07
From: SunnySandyEggo


Hi,

Welcome to UtterAccess!
welcome2UA.gif

Just a thought but how about backing up the table on your end and restoring it on their end?

--------------------
Just my 2 cents... "And if I claim to be a wise man, it surely means that I don't know" - Kansas
Access Website | Access Blog | Email
Go to the top of the page
 
GroverParkGeorge
post Jul 11 2018, 08:15 AM
Post#4


UA Admin
Posts: 33,041
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: 1


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
 
ukkid35
post Jul 12 2018, 03:47 AM
Post#5



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
 
ukkid35
post Jul 13 2018, 06:24 AM
Post#6



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
 
MadPiet
post Jul 13 2018, 04:02 PM
Post#7



Posts: 2,427
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
 


Custom Search
RSSSearch   Top   Lo-Fi    22nd July 2018 - 05:57 AM