Using PowerShell insert string values in SQL Server DB.

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?

$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               
        }

Raf · Accepted Answer · 2014-10-03 13:12:22Z

10

Try this, it will ride on a single connection so you will avoid the expensive overhead as per @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()

edited Oct 3, 2014 at 13:12

answered Apr 10, 2014 at 9:30

Raf

10.2k1 gold badge34 silver badges42 bronze badges

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

2 Comments

Flat Cat Over a year ago

I was getting errors with this until I put the open and close within the foreach block. (I tried pasting it here but the formatting is awful in these comment boxes.)

Raf Over a year ago

You will negate the benefits(the speed) of a single connection if you open/close on each insert. Open a new question and I'm sure someone will be able to help you with the errors.

vonPryz · Accepted Answer · 2014-04-10 08:33:40Z

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, bcp and update the destination table with a TSQL statement. Let Sql Server do all the heavy lifting.

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

Collectives™ on Stack Overflow

Using PowerShell insert string values in SQL Server DB.

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related