CODEWITHSUNDEEP
sqlpowershell
H. Schutte
H. Schutte

Reputation: 51

foreach with do while for building insert query

I have this PowerShell script

 $CSV = Import-Csv "records.csv"
        Foreach ($Row in $CSV) {
            $Q = "INSERT INTO database..table([id], [FirstName], [LastName]) VALUES ('"+ ($Row.'id') + "','" + ($Row.'First Name') + "','" + ($Row.'Last Name') + "')"
            Invoke-QsSqlQuery -query $Q -SQLServer <servername> -database <databaseName>
            }

Note: Invoke-QsSqlQuery is my own function.

My problem is that I am invoking a SQL command for every row. This has created a performance issue.

I want to build $Q so that it will have 1000 rows and then invoke it into SQL server. A bulk is not possible because, with bulk, the file needs to be copied locally on the server and that is not allowed.

With a do while I can count to 1000 that's not to hard but what if my remaining record are less than 1000?

How can I build a query that will update multiple records at once?

Upvotes: 3

Views: 852

Answers (2)

Mathias R. Jessen
Mathias R. Jessen

Reputation: 174465

This should be fairly simple

  1. Assign all the statements to a string array (your foreach loop is totally appropriate here)
  2. Split the string array into multiple string arrays of 1000 or less
  3. Concatenate each group
  4. Execute query
$CSV = Import-Csv "records.csv"

$SQLServer   = "dbserver.corp.company.tld"
$SQLDatabase = "database"

# Set up a string format template
$InsertTemplate = "INSERT INTO database..table([id], [FirstName], [LastName]) VALUES ('{0}','{1}','{2}')"

# Generate all insert statements and store in string array
$AllInserts = foreach($Row in $CSV){
    $InsertTemplate -f $Row.id,$Row.'First Name',$Row.'Last Name'
}

# Split array into an array of 1000 (or fewer) string arrays
$RowArrays = for($i=0; $i -lt $AllInserts.Length; $i+=1000){
    ,@($AllInserts[$i..($i+999)])
}

# Foreach array of 1000 (or less) insert statements, concatenate them with a new line and invoke it
foreach($RowArray in $RowArrays){
    $Query = $RowArray -join [System.Environment]::NewLine
    Invoke-QsSqlQuery -query $Query -SQLServer $SQLServer -database $SQLDatabase
}

Upvotes: 2

Paolo
Paolo

Reputation: 2254

depending on some factors you may be able to use bulk insert instead of multiple insert.

the requirements to satisfy are 2:
- the file to import must be on the sql server
- CSV files must comply with specific requirements stated on msdn for the format of the file to be imported

should you be able to comply with the above requirements you may be able to import the whole file with a statement simple as this one:

BULK INSERT database..table
FROM 'C:\FileToImport.csv';
GO

Upvotes: 1

Related Questions