How do I handle large SQL SERVER batch inserts?

Question

I'm looking to execute a series of queries as part of a migration project. The scripts to be generated are produced from a tool which analyses the legacy database then produces a script to map each of the old entities to an appropriate new record. THe scripts run well for small entities but some have records in the hundreds of thousands which produce script files of around 80 MB.

What is the best way to run these scripts?

Is there some SQLCMD from the prompt which deals with larger scripts?

I could also break the scripts down into further smaller scripts but I don't want to have to execute hundreds of scripts to perform the migration.

Answer 1

If possible have the export tool modified to export a BULK INSERT compatible file.

Barring that, you can write a program that will parse the insert statements into something that BULK INSERT will accept.

Answer 2

It sounds like this is generating a single INSERT for each row, which is really going to be pretty slow. If they are all wrapped in a transaction, too, that can be kind of slow (although the number of rows doesn't sound that big that it would cause a transaction to be nearly impossible - like if you were holding a multi-million row insert in a transaction).

You might be better off looking at ETL (DTS, SSIS, BCP or BULK INSERT FROM, or some other tool) to migrate the data instead of scripting each insert.

You could break up the script and execute it in parts (especially if currently it makes it all one big transaction), just automate the execution of the individual scripts using PowerShell or similar.

Answer 3

Just execute the script. We regularly run backup / restore scripts that are 100's Mb in size. It only takes 30 seconds or so.

If it is critical not to block your server for this amount to time, you'll have to really split it up a bit.

Also look into the -tab option of mysqldump with outputs the data using TO OUTFILE, which is more efficient and faster to load.

Answer 4

Thanks for the feedback,

@Chris: What is the format of a bulk insert file that you mention?

@Cade: Powershell was one option I was thinking of to execute each smaller script.

Answer 5

I've been looking into the "BULK INSERT" from file option but cannot see any examples of the file format. Can the file mix the row formats or does it have to always be consistent in a CSV fashion? The reason I ask is that I've got identities involved across various parent / child tables which is why inserts per row are currently being used.

Answer 6

BULK INSERT uses BCP format files which come in traditional (non-XML) or XML. Does it have to get a new identity and use it in a child and you can't get away with using SET IDENTITY INSERT ON because the database design has changed so much? If so, I think you might be better off using SSIS or similar and doing a Merge Join once the identities are assigned. You could also load the data into staging tables in SQL using SSIS or BCP and then use regular SQL (potentially within SSIS in a SQL task) with the OUTPUT INTO feature to capture the identities and use them in the children.