使用PowerShell在SQL Server DB中插入字符串值。

Question

我有一长串需要在单列SQL Server表中插入的值。我使用以下代码。但是，这需要很长时间。有没有更简单的方法来实现这一目标？

$list = 'aaa','bbb','cccc','ddddd','eeeee','ffff'....

    foreach($i in $list) {
        $sql ="if not exists (select 1 from [table_nm] where column_nm = '$i' ) 
            begin 
              insert table_nm
              select '$i'
            end
            "

        Invoke-Sqlcmd -ServerInstance $server -Database db_nm -query $sql               
        }

Answer 1

尝试此操作，它将在单个连接上运行，因此您可以避免按照@vonPryz进行的昂贵开销：

$list = 'aaa','bbb','cccc','ddddd','eeeee','ffff'....
$server = "server1"
$Database = "DB1"

$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($i in $list) {
    $sql ="if not exists (select 1 from [table_nm] where column_nm = '$i' ) 
        begin 
        insert table_nm
        select '$i'
        end
    " 
    $Command.CommandText = $sql
    $Command.ExecuteReader()
}
$Connection.Close()