Does anyone know if there is a maximum number of rows that can be inserted in a single statement?
I have a procedure that runs in an overnight job. There are 4 SELECT statements that are unioned together and inserted into a table. It's about as straightfoward as that. There's no transaction around the INSERT, there are no parameters.
All of a sudden the step got 'stuck.' It normally finishes running in about 50 minutes, and I realized when I got in this morning that it had been running for 8 hours. This is time-sensitive, so in the interest of moving things along I stopped the job, truncated the table, and started the insert again. After nearly two hours, I stopped it again. It was again stuck. But it got stuck at the same record count (2,763,363).
Does anyone have any troubleshooting ideas/suggestions? It's imperative I resolve this as quickly as possible. Initially I thought it was just bad data and for some reason rather than the job bombing it would just get stuck. But based on our data, it appears as though the last record in the table is the last available record to be inserted.
This procedure executes successfully on both of our development databases, one of which is SQL 2005 and the other SQL 2008. But they haven't been refreshed since the weekend and the total record count is lower, which is what led to the question about a maximum number of records being inserted. Though again, I'd like to think that if a maximum was hit, the job would've failed rather than just hanging.
Again, any input would be GREATLY appreciated!