CODEWITHSUNDEEP
sqlsql-servert-sql
Timo.Klement
Timo.Klement

Reputation: 637

How to update and insert in T-SQL in one query

I have a database that needs from time to time an update. It may also happens that there are new data while the update runs.

In MySQL there is a option

INSERT INTO IGNORE

I can't find something like this in T-SQL. No Problem to update ID 1-4 but then there is a new record for ID 5. The UPDATE query don't work here. And when I try to INSERT all data again I get a DUPLICATE KEY error.

Additional Infos: I've forgotten to say that my data come from external sources. I call an API to get data from it. From there I have to insert these data into my database.

I have to admit that I don't understand MERGE. So my solution for now is to use TRUNCATE first and then insert all data again. Not the best solution but MERGE works, so far I understand it, with two tables. But I have only one table. And to create a table temporarly to use MERGE and later drop that table is in my eyes a bit to much for my little table with 200 records in it.

Upvotes: 0

Views: 117

Answers (2)

EarlOfEnnui
EarlOfEnnui

Reputation: 555

Is a stored procedure an option?

CREATE PROCEDURE dbo.Testing (@ID int, @Field1 varchar(20))
AS
BEGIN
    UPDATE tblTesting
    SET Field1 = @Field1
    WHERE ID = @ID

    IF @@ROWCOUNT = 0
        INSERT INTO tblTesting (ID, Field1) SELECT @ID, @Field1
END

Upvotes: 0

shree.pat18
shree.pat18

Reputation: 21757

You can use MERGE keyword. Basically, you need to specify the column(s) on which to join the source of data with target table, and depending on whether it is matching (existing record) or not matching (new record), you run an UPDATE or INSERT.

Reference: http://msdn.microsoft.com/en-us/library/bb510625.aspx

Upvotes: 4

Related Questions