Update if exits or Insert if not

Question

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?

Whats wrong with writing a `select`, then an `update` or `insert` depending on if it exists? — crthompson, Apr 18 '14 at 18:06
That was my first idea, but i wanted to make sure that was the right way to go about doing it. — Itomship, Apr 18 '14 at 18:30
I think you'll find that a `Merge` while neat, is harder to maintain over time. KIS - Keep it simple. — crthompson, Apr 18 '14 at 19:45

Siyual · Accepted Answer · 2014-04-18T18:29:44.117

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
    ;
";

score 1 · Answer 2 · answered Apr 18 '14 at 18:22

1

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, ...')

answered Apr 18 '14 at 18:22

vav

4,584
2
19
39

You don't have to make seperate trips to the database, I can do this all in one in SQL. Also he tagged Sql not Oracle... – Trevor Apr 18 '14 at 19:03
@MrCoDeXeR, sql does not mean sql server. Granted, it is LIKELY, having also tagged vb.net, that its sql server. But not guaranteed. – crthompson Apr 18 '14 at 19:43

Update if exits or Insert if not

2 Answers2