Reputation: 99
I am coding an application which will insert if the primary key isn't found or update if it is found. Its the same type of situation that is asked here on this site.
Question.
Right now i am using the following code for a insert. (Names have been changed but same format)
Using con As New SqlConnection
Using cmd As New SqlCommand
Try
con.ConnectionString = "removed"
con.Open()
cmd.Connection = con
cmd.CommandText = "INSERT INTO [table] ([primary key],[value]) VALUES (@primary key, @value)"
cmd.Parameters.AddWithValue("@primary key", code to grab key)
cmd.Parameters.AddWithValue("@value", code for value)
cmd.ExecuteNonQuery()
con.Close()
Catch ex As Exception
MessageBox.Show("Error while inserting record on table..." & ex.Message, "Insert Records")
End Try
End Using
End Using
The complete code above works for me. But now i need it to only do inserts of the primary key isn't found.
Based on the above link, is the answer to do something like this
cmd.CommandText = "Merge Table as target using (values ('key') as source(key) on taget.idfield = code for key...
This doesn't feel right to me, (possibly because its all on one line). Whats the best way to accomplish this task?
Upvotes: 0
Views: 113
Reputation: 16917
Merge
is definitely a great way of accomplishing this task. Your code should look something similar to this:
cmd.CommandText =
@"
Merge into Table as Target
Using (Select @PrimaryKey As PrimaryKey) As Source
On Target.PrimaryKey = Source.PrimaryKey
When not matched then
Insert (PrimaryKey, Value)
Values (@PrimaryKey, @Value)
When matched then
Update
Set Value = @Value
;
";
Upvotes: 2
Reputation: 4694
select and insert/update requires 2 trips to the database.
So here is just one:
MERGE INTO yourTable target
USING (select val1 as key1, val2 as key2... from dual) source
ON (target.primary_key = source.key1)
WHEN MATCHED THEN UPDATE SET target.column2 = source.key2....
WHEN NOT MATCHED THEN INSERT (target.primary_key, target.column2, ...)
VALUES(source.key1, source.key2, ...)
I am converting your incoming parameters into dataset using oracle syntax, on other databases it will be different (SQL Server, for example: 'select val1 as key1, ...')
Upvotes: 1