Building batch insert statement powershell to sql

Question

I have a powershell script that writes every file and its attributes recursively starting from a specific directory. This works but the directories could have as many as 1,000,000 files. What I want to do is batch them at 1000 inserts per transaction. Here is the 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 are to implement some sort of counter that will keep appending the insert until it reaches 1000 and then jump out of the loop and execute. I cannot figure out with this current setup how to batch at 1000, execute and then pick back up with the get-childitem loop.

Void Ray · Accepted Answer · 2016-09-22 16:21:22Z

4

Something like this 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 into myTable(id) values($item) `n";
    $counter += 1;    
}

Execute-SqlQuery $sql;

answered Sep 22, 2016 at 16:21

Void Ray

10.3k4 gold badges35 silver badges55 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

VinnyGuitara Over a year ago

This is very close to what I ended up using so I am going to select this as the answer. Thanks!

TechSpud · Accepted Answer · 2016-09-22 16:25:04Z

$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

# new variables to handle batching
$batchcounter=0
$batchsize=1000
$sqlValues = New-Object Collections.ArrayList

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

    $sqlValues.Add("('$fileName', '$fileSize', '$fileMode', '$fileDirectory', '$fileCreationTime', '$fileLastAccessTime', '$fileLastWriteTime')")

    $batchcounter++

    # if the counter hits batchsize, run the insert, using lots of:
    # insert into table
    # values (1,2,3)
    #      , (4,5,6)
    #      , (7,8,9)

    if ($batchcounter % $batchsize -eq 0) {
        $sql = "insert TestPowerShell values {0}" -f ($sqlValues.ToArray() -join "`r`n,")
        $Command.CommandText = $sql
        Write-Host $sql
        $Command.ExecuteNonQuery()
        $sqlValues.Clear()
    }
}

# catch any remaining files
if ($batchcounter -gt 0) {
    $sql = "insert TestPowerShell values {0}" -f ($sqlValues.ToArray() -join "`r`n,")
    $Command.CommandText = $sql
    Write-Host $sql
    $Command.ExecuteNonQuery()
    $sqlValues.Clear()
}

$Connection.Close()

I am interested in this approach. I have already solved the problem with something else but I want to possibly build on this. I am going to take a look at this now.

VinnyGuitara · Accepted Answer · 2016-09-22 16:20:21Z

For anyone interested - this is one way to do it:

function WriteBatch {
    echo $sql
    $Command.CommandText = $sql
        $Command.ExecuteNonQuery()
}

$server = ""
$Database = ""
$Path = ""
$Counter = 0

$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
[string]$sql =     "
                begin 
                    insert into TestPowerShell(NameString, FileSize, Mode, Directory, CreationTime, LastAccessTime, LastWriteTime)
                    values " 

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 = $sql + "('$fileName', '$fileSize', '$fileMode', '$fileDirectory', '$fileCreationTime', '$fileLastAccessTime', '$fileLastWriteTime'),"
    $sql += "`n"
    $Counter++

    If($Counter -eq 900) {
        $sql = $sql.Trim().Trim(',')
        $sql = $sql + " End"
        WriteBatch
        $Counter = 0
        $sql = "
                begin 
                    insert into TestPowerShell(NameString, FileSize, Mode, Directory, CreationTime, LastAccessTime, LastWriteTime)
                    values " 
    }

}

if ($Counter -gt 0){
    $sql = $sql.Trim().Trim(',')
    $sql = $sql + " End"
    WriteBatch
}

$Connection.Close()

Collectives™ on Stack Overflow

Building batch insert statement powershell to sql

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related