在 SQL Server DB 中使用 PowerShell 插入字符串值。
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22974931/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using PowerShell insert string values in SQL Server DB.
提问by RaviLobo
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?
我有一长串需要插入到单列 SQL 服务器表中的值。我使用以下代码。然而，这需要很长时间。有没有更简单的方法来实现这一目标？
$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
}
回答by Raf
Try this, it will ride on a single connection so you will avoid the expensive overhead as per @vonPryz:
试试这个，它会在一个连接上运行，这样你就可以避免@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()
回答by vonPryz
The query takes a long time as you'll open a new Sql connection for each query. Load the source data into a staging table with, say, bcpand update the destination table with a TSQL statement. Let Sql Server do all the heavy lifting.
查询需要很长时间，因为您将为每个查询打开一个新的 Sql 连接。bcp使用 TSQL 语句将源数据加载到临时表中，然后更新目标表。让 Sql Server 完成所有繁重的工作。
A simple solution is based on common table expression like so,
一个简单的解决方案是基于像这样的公用表表达式，
-- Sample tables
create table myTable(data varchar(32))
create table staging(data varchar(32))
-- Some demo values
insert myTable values ('a')
insert myTable values ('b')
insert myTable values ('c')
insert myTable values ('d')
-- More demo values, note there is a duplicate
-- You'd fill this table with bcp. For illustration purposes,
-- data is inserted instead of bulk copying.
insert staging values ('e')
insert staging values ('c')
insert staging values ('f')
-- Let's look the table first
select data from mytable
-- Create a CTE that contains values from staging that are not in myTable
;with mt (sdata) as(
select data from staging s where data not in (select data from mytable)
)
-- Insert new values
insert into mytable(data) select sdata from mt
-- Let's look the final result
select data from mytable
