Better to use bulk insert or cursor 2

[ddiamond]

I come from an Oracle and DB2 backgroud. I am new to SQL Server. When using Oracle and DB2, I was always told to avoid large bulk inserts because you are likely to blow your rollback segments. I was told to instead use a cursor and commit after every 50,000 rows or so.

Now on SQL Server I've been advised to only use cursors as a last resort because they are so much slower than bulk inserts. On sql server, is it safe to run large bulk inserts? Is blowing the transaction logs not an issue?

 

[SQLSister]

Depends on what you are doing. If you use the bulk insert command to insert data from a file, then you aren't going to have a problem as it isn't logged the same way as a regular insert. If you are trying to insert a large number of records from another table using an insert statement, you may need to use a combination of a set-based insert and a loop or cursor to insert a batch of records at a time. BAtch size will depend on how hard the insert is based on indexing, triggers, etc. I usually start at 2000 rows and work up or down depending on how fast it goes. I've had tables where 500 wa the best I could do and others where I inserted 50000 in a batch easily. What you never want to do with a large insert is use a cursor that goes row-by-row.

"NOTHING is more important in a database than integrity." ESquared

 

[gmmastros]

There's a lot of negative hype about cursors. Most of it is well deserved. Because they are easy to abuse. Used properly, and under the right circumstances, they're not so bad.

Anyway... I suggest you read this:

faq183-3141

It doesn't use a cursor. The techniques discussed in that FAQ uses a while loop instead.


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[ddiamond]

Thanks for the tips. The FAQ demonstrated some interesting alternatives to cursors.