Inserting Lakhs of Records At a time

Question

I have 10 lakhs insert queries in a .sql file. Is there any way to install all the queries at a time in sql server management studio? When I try to do it it is raising System.OutOfMemory.Exception Even I tried with sqlcmd command prompt but it given same result. Thanks in Advance for suitable answer.

Nitin Sontakke · Answer

If data base recovery model is 'Simple' the dml operations either don't get logged at all or get almost immediately trucated as checkpoint process occurs behind the scenes. However, if it is not, then your log file keeps on bloating. Furthermore if there are not commits occasionally in between the inserts, the whole thing is assumed to be a single operation resulting in taking huge time. Also your rollback will be taking a loads of time should you abort this process. Having faced problems, I haven't really played with these things and have resorted to perform tasks in smaller batches of sql statements.

Nitin Sontakke · Answer

What is the recovery model of the database that matters in this case. Have you put the go statements in between? This is very important. Preferably after few thousand, may be 5000 or something like that. Remember when you use go your that many records are already gone in db. You should have a mechanism in place where one record is not inserted twice. Have you used insert into tablename for each and ever row? Is it possible to have it in more concise syntax like insert into tablename (columns) values (values), (values), (values). This appproach is better. With so many records, have already look into bulk insert? Or have a csv file of data import it using import / export wizard in SSMS?

Nitin Sontakke · Answer

In addition as your log file keeps on growing if it is not large enough, it has to claim space from OS, which again is a very time consuming operation.

Manish Kumar · Answer

Hi Nitin, Just want to know what is the role of recovey model of database here.