November 19, 2008 at 3:47 pm
Hello,
I got this incredibly larger table (4 million records) which I want to copy to a different database under the same server. One way to do it is to use the import wizard which will take long time like 5-6 hrs since the file is too large. I was thinking to zip the file and copy it and put it under the database I want. The problem is what file I need to zip? I guess its the .mdf file is there an easier way to do this? Could somebody give me an advice? Thanks!
November 19, 2008 at 3:53 pm
You don't want to copy the MDF file if you are only looking to move one table over. Use bcp.
November 19, 2008 at 3:57 pm
Your plan won't work, but I can offer some advice.
4 million rows isn't tiny, but it isn't that big, either. It shouldnt' take 5-6 hours to load from one db to another using bulk insert operations unless there's something wrong. I'd suggest that maybe you need to do some reindexing or check disk IO.
If that doesnt' work try using SSIS to send over only the changed data, if your table has a 'last updated' stamp it might work and be reasonably simple.
Another thing I can suggest that's easy is SQL Litespeed which can do table-level restores and is wicked fast. It costs money though.
~BOT
Craig Outcalt
November 20, 2008 at 7:28 am
Hi SSCrazy,
Could you specify more on bcp?
Thanks!
November 20, 2008 at 8:33 am
bcp.exe is a Microsoft-provided utility used to bulk insert data.
Bulk insert is what's going on behind the scenes in the import/export wizard anyway...
There are advantages to bulk inserting data, but let's just say its faster than doing hundreds of inserts.
there are some recoverability concerns about bulk-logged transactions and you will not be able to do a point in time recovery of those bulk transactions.
Here's an article on that for SQL 2000 couldn't find the one for 2005, but nothing's changed that I know of.
Since I'm only 90% certain that bulk insert is what's going on behind the scenes in an import/export wizard, I think you'll have to verify this to see if there's any performance gains to be made for using bcp vs. the import/export wizard.
I still say you have some disk io issues. you might need to drop some indexes and recreate them after the load or something... it really is too long to take 5 hours for 4 million rows. Look for performance improvements where the data is written, as that's probably the bottleneck.
Craig Outcalt
November 20, 2008 at 10:07 am
Keep in mind that you do not have to change your recovery mode to bulk-logged to use bcp. It is still faster than using INSERT INTO even when in full recovery mode. With that said, this sounds like a one time move so point-in-time recovery may not be a factor.
http://msdn.microsoft.com/en-us/library/ms162802(SQL.90).aspx
November 20, 2008 at 12:22 pm
So, in my case, it should look something like this then..
bcp mydb1.table1 in mydb2.table1 -T -c
In my case, the destination table does not exist. I have been following the import wizard where you just specify the source table and sql server creates the destination table on the fly while its importing the data. So, this way I don't have to worry about creating all the fields and assign the datatype. Say, I want to copy data from table1 which is in mydb1 database to a database mydb2, would the above command work? Do I need to create the table table1 in mydb2 before i run the bcp command?
Thanks!
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply