CODEWITHSUNDEEP
sqlsql-serverdatabase
Jaison
Jaison

Reputation:

Insert value from one table to another by changing the flag

I have to insert value from one table to another table which both having two fields first field name is ID and and second field name is Flag.

While inserting if the ID value already exists in any of the destination table rows it will insert the new row in source table as same ID and Flag as NEW

if the ID value is not matching in any of the destination table rows it will insert row as ID and Flag as OLD.

For example (from comment below):

Table 1
-------- 
A O 
B O 
C O 

Table 2 
A N 
B N 
D N 

After Insert 
------------ 
A N 
B N 
C n 
A N 
B N 
D N

Upvotes: 0

Views: 1196

Answers (1)

Russ Cam
Russ Cam

Reputation: 125498

If I follow you correctly, given the following

Source Table

ID  |  Flag
-----------
1      NULL  
2      NULL
3      NULL


Dest Table

ID  |  Flag
-----------
1      NULL

You want to insert records from Source into Dest.

  • If the ID already exists in Dest, insert the ID into Source with a flag of NEW
  • If the ID does not exist in Dest, insert the ID into Dest with a flag of OLD

First get the IDs that exist in both Dest and Source and insert NEW records into Source

    INSERT INTO Source (ID, Flag)
SELECT 
    s.ID, 'NEW'
FROM
    Source s
INNER JOIN
    Dest d
    ON d.ID = s.ID

then get the IDs that don't exist in Dest but exist in Source and insert OLD records into Dest

    INSERT INTO Dest (ID, Flag)
SELECT 
    s.ID, 'OLD'
FROM
    Source s
LEFT JOIN
    Dest d
    ON d.ID = s.ID
WHERE
    d.ID IS NULL

This results in

Source Table

ID    |     Flag
----------- ----
1           NULL
2           NULL
3           NULL
1           NEW

Dest Table

ID    |     Flag
----------- ----
1           NULL
2           OLD
3           OLD

Is that what you were looking for?

I have to be honest and say that this doesn't feel like a nice way to be doing things, for example, I think it would be better to update records that you already have in the tables rather than insert new ones with the same ID, although I don't know what you're trying to achieve or what keys you have on the tables (if any). If you could provide more info, I may be able to help further.

Upvotes: 2

Related Questions