CODEWITHSUNDEEP
sqlvb.net
Itomship
Itomship

Reputation: 99

Update if exits or Insert if not

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

Answers (2)

Siyual
Siyual

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

vav
vav

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

Related Questions