Inserting data into SQL-Server table dynamically

Question

I know I can do all this via SSMA quite quickly, but I wanted to see if I could do it efficiently in PowerShell myself as a little project.

Basically, the entire script takes tables from an Access database and creates a database in SQL Server with the same data. Everything from creating the table structure to cleansing the data is running quick. The slow part is inserting the data, which was inevitable but I want to see if I can make it a bit faster.

Here is the code:

<# Insert data into tables #>
function Insert_Data {
    param ( $data, $tableName )
    $columns = ($data.PSObject.Properties | where {$_.name -eq "Columns"}).value.columnName | Sort-Object

    $Insert = "INSERT INTO $tableName
    VALUES "

    $i = 0
    $x = 0

    foreach ($item in $data) {
        $Insert += "
        ("
        foreach ($item in $columns) {
            $Insert += "'" + $data.rows[$x].$item + "',"
        } 
        $Insert = $Insert.Substring(0,$Insert.Length-1)
        $Insert += "),"
        if ($i -eq 900) {
            $Insert = $Insert.Substring(0,$Insert.Length-1)
            $Insert += ";"
            Invoke-SQLCMD -Query $Insert -ServerInstance "." -database tmpAccessData -erroraction "Stop"
            $Insert = "INSERT INTO $tableName
                              VALUES "


            $i = 0
        }
        $i++
        $x++
    }

    $Insert = $Insert.Substring(0,$Insert.Length-1)
    $Insert += ";"

    Invoke-SQLCMD -Query $Insert -ServerInstance "." -database tmpAccessData -erroraction "Stop"

    Remove-Variable -Name data
}

It generates a large SQL query and inserts that into the specified table once it hits 900 values or the end of the Access table.

The data variable is the full Access table pulled into an Object:

$data_Clients       = Get-AccessData -Query "SELECT * FROM Client"    -Source $($settings.FastrackFiles.access_Fastrack);

And tablename is just the name of the table in the destination SQL database.

Answer 1

The main problem with this code is that it's not screening column values, unfortuantely buit in mechanisms in Invoke-SQLCMD aren't viable. It allows to use -Variable but it also accepst stings, like -Variable "p0='var_value'" which don't help at all with escaping, even making it worse.

but here are opesource alternative to Invoke-SQLCMD: https://github.com/RamblingCookieMonster/PowerShell/blob/master/Invoke-Sqlcmd2.ps1

It can be installed by executing

Install-Module WFTools -Force -AllowClobber

Example of unescaped query with Invoke-SQLCMD

> Invoke-Sqlcmd -Database tb-sql-db -Server XXXXX -Username YYYY -Password ZZZZ -Query "SELECT id, um.text FROM archive_srv_db.tbUniMessage um WHERE id = '3161e665-a30e-48c4-87f2-0008d62da8a6'"

id                                   text
--                                   ----
3161e665-a30e-48c4-87f2-0008d62da8a6 Hello!

Same query using parameters and Invoke-Sqlcmd2:

$secpasswd = ConvertTo-SecureString "ZZZZ" -AsPlainText -Force
$mycreds = New-Object System.Management.Automation.PSCredential ("YYYYY", $secpasswd)
Invoke-Sqlcmd2 -Database tb-sql-db -Server XXXXX -Credential $mycreds -Query "SELECT id, um.text FROM archive_srv_db.tbUniMessage um WHERE id = @p0" -SqlParameters @{ p0="3161e665-a30e-48c4-87f2-0008d62da8a6"}

id                                   text
--                                   ----
3161e665-a30e-48c4-87f2-0008d62da8a6 Hello!

If you are concerned with escaping issues, if any had occur, you should migrate to Invoke-Sqlcmd2. But please take a note, that parametrized query in ADO.NET can hold up to roughly 2000 parameters. So your loop condition should be ($i * $columns.Length) -gt 1900 (just assumption that you won't have more than 100 columns. It's to late here to write exact code :-).

And in your loop you should append to query @p$counter, where counter is incrementing variable defined outside of a loop and incremetring inside loop on each value. and build dictionary, populating it with named parameters.

Example:

#define dictionary before outer loop
$dict = @{}
....

#add value inside inner loop
$Insert += "?,"
$dict.Add("@p$counter",$data.rows[$x].$item)