Question:
I have a long list of values that need to be inserted in a single column SQL server table. I use the following code. However, it takes a long time. Is there an easier way to achieve this?
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
$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:
Try this, it will ride on a single connection so you will avoid the expensive overhead as per @vonPryz:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
$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() |