Insert into DB

Ask your PowerShell-related questions, including questions on cmdlet development!
Forum rules
Do not post any licensing information in this forum.

Any code longer than three lines should be added as code using the 'Select Code' dropdown menu or attached as a file.
This topic is 6 years and 8 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked
User avatar
sekou2331
Posts: 318
Last visit: Sat Oct 28, 2023 7:46 am

Insert into DB

Post by sekou2331 »

Hi,

I am trying to insert into a DB with the code below. The first run of the scripts insert works. But I run the script everyday because one of the columns changes day to day. My issue is that the other columns do not change and they are primary keys. Is there a way in powershell to ignore whats there?

$conn = New-Object System.Data.SqlClient.SqlConnection
$conn.ConnectionString = "Server=Servername;Database=DBName ;trusted_connection=true;"
$Conn.Open()
$Command = New-Object System.Data.SQLClient.SQLCommand
$Command.Connection = $Conn

foreach ($a in $b)
{
if ($a.Name -match "computer")
{
$a | foreach {

$Command.CommandText = "INSERT INTO Table1(Computer,Name,Version,) VALUES('{0}','{1}','{2}')" -f $_.Computer, $_.Name, $_.Version
$Command.ExecuteNonQuery() | out-null

}

}
else
{
$a | foreach {

$Command.CommandText = "INSERT INTO Table2(Computer,Name,Version,) VALUES('{0}','{1}','{2}')" -f $_.Computer, $_.Name, $_.Version
$Command.ExecuteNonQuery() | out-null

}

}
}

$conn.Close()





ERROR: Exception calling "ExecuteNonQuery" with "0" argument(s): "Violation of PRIMARY KEY constraint 'PK_Table2'. Cannot insert duplicate key in object
ERROR: 'dbo.Table2'. The duplicate key value is (Computer, Name).
ERROR: The statement has been terminated."
Script.ps1 (77, 4): ERROR: At Line: 77 char: 4
ERROR: +             $Command.ExecuteNonQuery() | out-null
ERROR: +             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR:     + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
ERROR:     + FullyQualifiedErrorId : SqlException
ERROR:
ERROR: Exception calling "ExecuteNonQuery" with "0" argument(s): "Violation of PRIMARY KEY constraint 'PK_Table2'. Cannot insert duplicate key in object
ERROR: 'dbo.Table2'. The duplicate key value is (Computer, Name).
ERROR: The statement has been terminated."
Script.ps1 (77, 4): ERROR: At Line: 77 char: 4
ERROR: +             $Command.ExecuteNonQuery() | out-null
ERROR: +             ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
ERROR:     + CategoryInfo          : NotSpecified: (:) [], MethodInvocationException
ERROR:     + FullyQualifiedErrorId : SqlException
ERROR:
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Insert into DB

Post by jvierra »

You would use an Update query with the WHERE selecting the primary keys.

$sql = "UPDATE Table2 SET column = '$value' WHERE keycol1 = 1 AND keycol2 = 2 AND keycol3 = 3"
jvierra
Posts: 15439
Last visit: Tue Nov 21, 2023 6:37 pm
Answers: 30
Has voted: 4 times
Been upvoted: 33 times

Re: Insert into DB

Post by jvierra »

The attached code file is closer to what you are looking for:
sqlsnip.ps1
(586 Bytes) Downloaded 148 times
This topic is 6 years and 8 months old and has exceeded the time allowed for comments. Please begin a new topic or use the search feature to find a similar but newer topic.
Locked