CODEWITHSUNDEEP
sqlsql-server-2008stored-proceduresforeachcursor
Robin
Robin

Reputation: 293

SQL Loop through table A & Insert/ Update in table B

What would be the best way in a stored proc to loop through all the rows of table "A" & Check by Id if record exists in table "B" & depending upon result (found in table B or not) perform a "Insert" or "Update" into table "B".

For example:

//LOOP START
    //if ID Exists in Table B.
        //Take current row table a values and update table "B"
    Else
        //Take current row table a values and insert into table "B"
//LOOP END

Thanks

Upvotes: 0

Views: 1506

Answers (1)

J Greene
J Greene

Reputation: 361

Use the MERGE INTO command on SQL 2008

MERGE INTO table_B as Target
USING (
    Select
        Id, Field1, Field2
    FROM table_A ) as Source
ON Source.Id = Target.Id
WHEN NOT MATCHED THEN
    INSERT( Id, Field1, Field2 )
    VALUES( Source.Id, Source.Field1, Source.Field2 )
WHEN MATCHED THEN
    UPDATE
    SET
        target.Id = Source.Id
        ,target.Field1 = Source.Field1
        ,target.Field2 = Source.Field2
;

Upvotes: 2

Related Questions