submit batch of sql inserts as string or submit one insert at the time to ExcuteNonQuery()

• ebooklub

  Hall of Fame

  Points: 3905

  More actions

  January 4, 2018 at 11:42 am

  #334327

  Case1
   $sql ="
    INSERT INTO dbo.DbSecurity (Servername,DatabaseName,ContactUser) VALUES ('Server1','Db1,'User1');
    INSERT INTO dbo.DbSecurity (Servername,DatabaseName,ContactUser) VALUES ('Server1','Db1,'User1');
    INSERT INTO dbo.DbSecurity (Servername,DatabaseName,ContactUser) VALUES ('Server1','Db1,'User1');
    INSERT INTO dbo.DbSecurity (Servername,DatabaseName,ContactUser) VALUES ('Server1','Db1,'User1');
    INSERT INTO dbo.DbSecurity (Servername,DatabaseName,ContactUser) VALUES ('Server1','Db1,'User1');
    INSERT INTO dbo.DbSecurity (Servername,DatabaseName,ContactUser) VALUES ('Server1','Db1,'User1');
    INSERT INTO dbo.DbSecurity (Servername,DatabaseName,ContactUser) VALUES ('Server1','Db1,'User1');
    INSERT INTO dbo.DbSecurity (Servername,DatabaseName,ContactUser) VALUES ('Server1','Db1,'User1');
         "
   $Command.CommandText = $sql
   Command.ExcuteNonQuery() SQL profiler show CPU = 0 Duration 8 as result of bacth Case2 $i =0
   if($i -le 8) {
       $sql ="INSERT INTO dbo.DbSecurity (Servername,DatabaseName,ContactUser) VALUES ('Server1','Db1,'User1');"
       $Command.CommandText = $sql
     $Command.ExcuteNonQuery()
     $i++
   }
   SQL profiler show CPU = 0 Duration 1 -- for each executionQuestion
  Do we save on script  execution time when submit batch of 500000  rows using call in (in case 1) looop 500000 times inserting row by row (Case2)?

• Ernest Libertucci

  Ten Centuries

  Points: 1394

  More actions

  January 4, 2018 at 11:53 am

  #1974513

  I suggest looking into SqlBulkCopy with a DataTable.