Update table if rows already exists while inserting if row does not exists from a dataset

Question

I am creating a stored procedure where I am inserting into a table from a SELECT statement. The table has 4 columns (e.g. Col1, Col2, Col3, Col4). Column1 has a unique value. and Column4 is the timestamp of when the rows are inserted.

What I want to achieve is this:-

1.) That when I run the stored procedure on a later date lets say on the 20032017 I add the rows to the table if Col1 does not exists in the table.

2.) If Col1 already exists in the table I update the values of Col2 and Col3 while keeping tha value of Col5 as is.

So assuming in the second run of the stored procedure the select statement returns this dataset. The table will show like this, Rows with Col1 value of 7 is inserted in the table. Rows with Col1 values of 1, 2, and 3 have the values of Col2 and Col3 updated while Col4 remain with the same value.

Probably I have to create a loop in the stored procedure where each row in the dataset returned by the SELECT statement is processed but I am new to stored procedures and I am having difficulties. Code examples are much appreciated so I can understand better.

Thanks

Try reading up on MERGE - sounds like what you need. msdn.microsoft.com/en-us/library/bb510625.aspx — Fizor
– Fizor, Commented Mar 14, 2017 at 14:37
@user2307236 Some points that need clarification: (1) In Step 2, I'm assuming you meant Col4, not Col5. (2) In your walkthrough, you have a table with ID's {1,2,3,7} already existing, yet you say that you then want to insert {7}. Don't you mean you want to update 7? (3) What is going on with third table? Where did it come from? And why are most of the dates changed? — KindaTechy
– KindaTechy, Commented Mar 14, 2017 at 16:29

jjj · Accepted Answer · 2017-03-14 14:50:45Z

I am not sure what do you mean by inserting data into table from select - Merge need source table from which you can update/insert/delete target.

try following if help

 MERGE tabelName WITH (HOLDLOCK) AS target USING (SELECT Column1, Column2,Column3,Column4) AS source (Column1, Column2,Column3,Column4) // here goes your source ON (target.Column1 = source.Column1) WHEN MATCHED THEN UPDATE SET Column2 = source.Column2, Column3 =source.Column3 WHEN NOT MATCHED THEN INSERT ( Column1, Column2,Column3,Column4) VALUES ( source.Column1, source.Column2,source.Column3,source.Column4) ;

Tks for your reply. By 'INSERTING DATA INTO TABLE FROM SELECT' I mean the the source table is a SELECT statment from a table.

Collectives™ on Stack Overflow

Update table if rows already exists while inserting if row does not exists from a dataset

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related