Sql-server – BULK INSERT – best usage

bcpbulkinsertsql-server-2008

I need to export 150 million rows of only int/bigint columns from one SQL instance to another. I am using BCP queryout to export the rows and BULK INSERT to import the rows to another table.

I split 150 mln row into 3 BCP Export files each of 50 million rows and 3 BULK INSERTS. It worked (15 min BCP out/ 40 min Bulk Insert), but I would rather export just one file and import as well one file.

Is it technically possible or I can have issues for the big amount of rows? I never Imported more than 50 mln rows from a BCP file and I am not sure how this can work out. Is there a limit for BCP export or Import?

Thanks,

Best Answer

It is technically possible, yes. But you may be able to leverage performance gains by having multiple files on multiple disks as opposed to just one file. Why not try using one file and see if it works, and what the performance is like?

Here is a link for info on BULK INSERT: http://msdn.microsoft.com/en-us/library/ms188365.aspx

Related Solutions

Sql-server – Importing large file while keeping table available for other operations

Holy cow, you've got a lot of questions in here:

How the table will be affected? During this import (updates and inserts) what could happen to other processes trying to read or write data to this table?

With careful index design and partitioning, you can get away with minimal disruption to other queries. Discussing how to implement partitioning is a little beyond what we can talk through here - check out Louis Davidson's book on SQL 2008 database design. It covers concurrency pretty well.

I know bulk insert functionality as being something extremely fast compared to normal inserts. Does SQL Server 2008 provides something similar for UPDATES?

Not really. You can't update in bulk because you could be changing rows all over the table. This is where index design comes in (as explained in Louis's book).

All the row to be imported will be of the same UserId, so having this table partitioned by userid will improve the performance right? As all the other queries related to other users which doesnt belong to the affected partition will not be affected... Is this correct?

Only if your indexes are also partitioned by UserId, and that's rarely the case. Usually you'd want indexes arranged in a different manner - but again, that's covered in the book.

Sql-server – Move data from one table to other in batch’s

while (1=1)
begin
    delete top(10000) from oldtable
       output deleted.columnA, deleted.columnB, deleted.columnC
       into newtable (columnA, columnB, columnC)
    if @@ROW_COUNT = 0
       break;
end

Best Answer

Related Solutions

Sql-server – Importing large file while keeping table available for other operations

Sql-server – Move data from one table to other in batch’s

Related Question