I have a query having cte with number of columns, I want to insert a record if ID from the results of that query does not exist in table that I am inserting, or if the ID exists I want to update data using that ID.
So far I have tried this:
WITH cte_base as(
SELECT DISTINCT ID, statusID
FROM testtable
)
SELECT *
FROM cte_base
IF EXISTS(SELECT * FROM Newtable WHERE EXISTS (SELECT ID FROM cte_base))
UPDATE newtable
SET statusID = 2
WHERE Newtable.ID = cte_base.ID
ELSE
INSERT INTO newtable(ID, statusID)
SELECT ID, statusID
FROM cte_base
WHERE Newtable.ID <> cte_base.ID
I have to run this query against live data, hence I would like to know if my logic is correct.
Basic merge example based on your provided code.
MERGE INTO NewTable AS T
USING
(
SELECT DISTINCT ID,statusID
FROM testtable
) AS S
ON S.ID = T.ID
WHEN MATCHED THEN SET
T.StatusID = 2
WHEN NOT MATCHED INSERT (ID,statusID)
VALUES (S.ID,S.statusID)
;
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加