sql server - Building batch insert statement powershell to sql -

i have powershell script writes every file , attributes recursively starting specific directory. works directories have many 1,000,000 files. want batch them @ 1000 inserts per transaction. here original ps:

$server = "" $database = "" $path = "c:\test"  $connection = new-object system.data.sqlclient.sqlconnection $connection.connectionstring = "server='$server';database='$database';trusted_connection=true;" $connection.open() $command = new-object system.data.sqlclient.sqlcommand $command.connection = $connection  foreach($file in get-childitem -verbose -recurse -path $path | select-object  name,length,mode,  directory,creationtime, lastaccesstime, lastwritetime) {     $filename = $file.name     $filesize = ([int]$file.length)     $filemode = $file.mode     $filedirectory = $file.directory     $filecreationtime = [datetime]$file.creationtime     $filelastaccesstime = [datetime]$file.lastaccesstime     $filelastwritetime = [datetime]$file.lastwritetime      $sql =  "                 begin                      insert testpowershell                     select '$filename', '$filesize', '$filemode', '$filedirectory', '$filecreationtime', '$filelastaccesstime', '$filelastwritetime'                 end             "      $command.commandtext = $sql     echo $sql     $command.executenonquery() }  $connection.close() 

my thoughts implement sort of counter keep appending insert until reaches 1000 , jump out of loop , execute. cannot figure out current setup how batch @ 1000, execute , pick get-childitem loop.

something should do:

function execute-sqlquery($query){     write-host "executing query:"      write-host $query; }  $data = @(1,2,3,4,5,6,7,8,9,10,11); $batchsize = 2; $counter = 0; $sql = "";  foreach($item in $data){      if($counter -eq $batchsize){         execute-sqlquery $sql;         $counter = 0;         $sql = "";     }      $sql += "insert mytable(id) values($item) `n";     $counter += 1;     }  execute-sqlquery $sql;