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: