Fastest Way to Export Table From Access to SQL Server

Question

Fastest Way to Export Table From Access to SQL Server

guerillaunit

SSCarpal Tunnel

Points: 4249
More actions
February 2, 2012 at 7:26 am

#250834

Hi there,
What's the fastest way to move a table from Access to SQL Server? I am using the "Export" function in Access to move tables, but this takes quite a long time with large tables. I have several 2GB tables I would like to move and each 2 GB table is taking 30-45 minutes to "Export"

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

Koen Verbeeck SSC Guru Points: 259125 More actions · Answer 1

Try the Import/Export wizard of SQL Server.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

johnitech.itech Hall of Fame Points: 3252 More actions · Answer 2

:hehe:

or use Use the Upsizing Wizard from office

see link for detals

http://office.microsoft.com/en-us/access-help/move-access-data-to-a-sql-server-database-by-using-the-upsizing-wizard-HA010275537.aspx

Koen Verbeeck SSC Guru Points: 259125 More actions · Answer 3

johnitech.itech (2/2/2012)
:hehe:
or use Use the Upsizing Wizard from office

I didn't know the upsizing wizard was that funny...

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

f2004406 SSC Rookie Points: 25 More actions · Answer 4

Try using BulkInsert with TabLock ..but do note that TabLock will make the table unavailable for various operations by different users during the time of data population..

johnitech.itech Hall of Fame Points: 3252 More actions · Answer 5

So Koen Verbeeck

No not funny ,So Why i sent you link to View everything in Details ?!

What's the wrong of that !

MysteryJimbo SSC-Insane Points: 24203 More actions · Answer 6

A few of my thoughts. I'm not sure which would be quicker without testing. I'd hazard a guess that a SSIS package could be tweaked to be the fastest

-Create a linked server and import directly from a SQL query.

-SSIS using a dataflow

-Import/Export wizard in SSMS

Koen Verbeeck SSC Guru Points: 259125 More actions · Answer 7

johnitech.itech (2/5/2012)
So Koen Verbeeck
No not funny ,So Why i sent you link to View everything in Details ?!
What's the wrong of that !

I was referring to the smiley that accompanied the link (and almost every link that you post). Made me wonder if the upsizing wizard is funny...

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP