Have a look at example:
DECLARE @ws TABLE(ID INT)
INSERT INTO @ws (ID)
VALUES(6589)
SELECT ID
FROM @ws
INSERT INTO @ws (ID)
SELECT ID
FROM (
SELECT 6589 AS ID UNION ALL
SELECT 6857 AS ID UNION ALL
SELECT 1220 AS ID UNION ALL
SELECT 4500 AS ID
) AS T
WHERE ID NOT IN(SELECT ID FROM @ws)
SELECT ID
FROM @ws
Results:
1.query - before insert
ID
6589
2.query - after insert
ID
6589
6857
1220
4500
Using CTE and list of ID's as a varchar variable:
DECLARE @ValuesToInsert VARCHAR(300) = '6589,6857,1220,4500'
;WITH VTI AS
(
SELECT CONVERT(INT, LEFT(@ValuesToInsert, CHARINDEX(',', @ValuesToInsert)-1)) AS TID, RIGHT(@ValuesToInsert, LEN(@ValuesToInsert) - CHARINDEX(',', @ValuesToInsert)) AS Remainder
WHERE CHARINDEX(',', @ValuesToInsert)>0
UNION ALL
SELECT CONVERT(INT, LEFT(Remainder, CHARINDEX(',', Remainder)-1)) AS TID, RIGHT(Remainder, LEN(Remainder) - CHARINDEX(',', Remainder)) AS Remainder
FROM VTI
WHERE CHARINDEX(',', Remainder)>0
UNION ALL
SELECT CONVERT(INT,Remainder) AS TID, NULL AS Remainder
FROM VTI
WHERE CHARINDEX(',', Remainder)=0
)
INSERT INTO @ws (ID)
SELECT ID
FROM
(
SELECT TID AS ID
FROM VTI
) AS T
WHERE ID NOT IN (SELECT ID FROM @ws)
SELECT ID
FROM @ws
Have a look at OriginalGriff's tip:
Using comma separated value parameter strings in SQL IN clauses[
^]