构建批量插入语句powershell到sql

我有一个 powershell 脚本，它从特定目录开始递归地写入每个文件及其属性。这可行，但目录可能包含多达 1,000,000 个文件。我想要做的是以每个事务 1000 次插入的方式对它们进行批处理。 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()

我的想法是实现某种计数器，它将不断追加插入，直到达到 1000，然后跳出循环并执行。我无法弄清楚当前的设置如何以 1000 进行批处理、执行然后使用 get-childitem 循环进行恢复。

像这样的事情应该做：

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;