Insert and Update in one query in sql server 2005

Question

Possible Duplicate:
Select / Insert version of an Upsert: is there a design pattern for high concurrency?

I have to insert data from one table to another on the basis of condition.

1.If Key is found update records
2.If key is not found insert the record.

I am using sql server 2005. So can not use merge statement. Please suggest the alternative to achieve this

The suggested duplicate is for "Insert SELECT" not "Insert/Update" - it's definitely not an appropriate duplicate link. — niico, May 02 '17 at 08:55

Andomar · Accepted Answer · 2012-12-10T10:55:57.803

5

To copy from SourceTable to DesitinationTable:

update  dst
set     col1 = src.col1
from    DestinationTable dst
join    SourceTable src
on      src.Key = dst.Key

insert  DestinationTable 
        (Key, col1)
select  Key
,       col1
from    SourceTable src
where   not exists
        (
        select  *
        from    DestinationTable dst
        where   src.Key = dst.Key
        )

edited Dec 10 '12 at 10:55

answered Dec 10 '12 at 10:50

Andomar

232,371
49
380
404

They are two tables not one table. – Mahmoud Gamal Dec 10 '12 at 10:52
probably want to do it inside a transaction but yes – Jodrell Dec 10 '12 at 10:52
@MahmoudGamal, how do you know that, its not in the question? – Jodrell Dec 10 '12 at 10:53
@Jodrell The OP said `I have to insert data from one table to another on the basis of condition.` – Mahmoud Gamal Dec 10 '12 at 10:54
@MahmoudGamal: Good point, answer updated – Andomar Dec 10 '12 at 10:55
Gr8 answer and illustration.. Thanks Andomar – Rajaram Shelar Dec 10 '12 at 12:43

score 5 · Answer 2 · answered Dec 10 '12 at 10:54

5

IF EXISTS(--Query to check for the existence of your condition here)
BEGIN
  --UPDATE HERE
END ELSE
BEGIN
  --INSERT HERE
END

answered Dec 10 '12 at 10:54

Kaf

33,101
7
58
78

Will it insert/update entire table data? – Rajaram Shelar Dec 10 '12 at 10:55
This is a structure to follow. You can do update a single row, a few rows or entire table. All depend on the condition – Kaf Dec 10 '12 at 10:57
I want to check where each row record exist in dest or not. how would you make this condition. I am just stuck here – Rajaram Shelar Dec 10 '12 at 11:21
Your question is already answered. Or you are not explaining the question properly. Can you show the query that you are using to check the condition? – Kaf Dec 10 '12 at 11:26

score 0 · Answer 3 · edited May 23 '17 at 12:26

Here is a stored procedure example from my answer to the similar question

   CREATE PROCEDURE dbo.update_table1 
    @Field1 int, --key1
    @Field2 int, --data fields
    @Field3 int, 
    @Field4 int

    AS

    SET NOCOUNT ON
    update table1 set Field2=@Field2,Field3=@Field3,Field4=@Field4 
            where Field1=@Field1;
    IF(@@Rowcount=0)
    BEGIN
         insert into table1(Field1,Field2,Field3,Field4) 
                    values (@Field1,@Field2,@Field3,@Field4);
    END
    GO

Insert and Update in one query in sql server 2005

3 Answers3