Inserting Lakhs of Records At a time

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.

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.

Hi Nitin,

 

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

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?