April 16, 2009 at 9:53 am
Hi friends,
I have one database in Access which is having around 20 tables with lots of records...some of them having 2 milion....i would like to transfer all of them into SQL....which is the best way?
SSIS, bulk insert, BCP???
Because that table will get refresh every month we need to set up a process to import all refreshed table froma access to SQL....i need to write one script which does everyhting...
do i need to desing 20 package for this? or One stored procedure which does bulk insert for all 20 tables?
Any suggestion? we are using SQL server 2005.
regards,
vijay
April 16, 2009 at 1:16 pm
I'd use an SSIS package that imports data from all twenty Access tables and schedule it in a monthly SQL Agent job. You can use the Import/Export Wizard in SSMS to create the initial package, then modify it if needed.
Greg
April 16, 2009 at 3:16 pm
Ther used to be an Access Upgrade tool that would do this for you. Not sure if Access still has it or not.
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply