How many inserts can you have in a sql transaction.

Question

I have a task to do that will require me using a transaction to ensure that many inserts will be completed or the entire update rolled back.

I am concerned about the amount of data that needs to be inserted in this transaction and whether this will have a negative affect on the server.

We are looking at about 10,000 records in table1 and 60,0000 records into table2.

Is this safe to do in a single transaction?

Answer 1

have you thought about using a bulk data loader like SSIS or the data import wizard that comes with sql server?

the data import wizard is pretty simple.

In management studio right click on the database you want to import data into. Then select tasks and import data. Follow the wizard prompts. If a record fails the whole transaction will fail.

I have loaded millions of records this way (and using SSIS).

Answer 2

it is safe, however keep in mind that you might be blocking other users during that time. Also take a look at bcp or BULK INSERT to make the inserts faster