Inserting a value from one table to another by changing a flag
I need to insert a value from one table into another table where both fields have the first field name is ID and the second field name is a flag.
On insert, if the id value already exists in any of the rows of the target table, it will insert a new row into the source table as the same id and flag as NEW
if the ID value does not match in any of the rows of the target table, it will insert the row as ID and the flag as OLD.
For example (from the comment below):
Table 1 -------- AO BO CO Table 2 AN BN DN After Insert ------------ AN BN C n AN BN DN
If I follow you correctly, ask next
Source Table
ID | Flag
-----------
1 NULL
2 NULL
3 NULL
Dest Table
ID | Flag
-----------
1 NULL
You want to insert records from Source
to Dest
.
- If
ID
already exists inDest
, insertID
inSource
withflag
NEW - If
ID
does not exist inDest
, insertID
inDest
withflag
OLD
First get ID
which exists both in Dest
and in Source
and insert NEW records inSource
INSERT INTO Source (ID, Flag)
SELECT
s.ID, 'NEW'
FROM
Source s
INNER JOIN
Dest d
ON d.ID = s.ID
then get ID
that doesn't exist in Dest
but exists in Source
and insert OLD records inDest
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
The result is
Source Table
ID | Flag
----------- ----
1 NULL
2 NULL
3 NULL
1 NEW
Dest Table
ID | Flag
----------- ----
1 NULL
2 OLD
3 OLD
Is this what you were looking for?
I have to be honest and say that this doesn't seem like a good way of doing things, for example, I think it would be better to update the records you already have in the tables rather than insert new ones using the same one ID
, although I don't know what are you trying to achieve or what keys do you have on the tables (if any). If you could provide more information, I can help further.
a source to share