g2_player g2_player - 3 months ago 8
SQL Question

INSERT INTO with WHERE IN clause

INSERT INTO dbo.ASTMListCustom
([ASTMID]
,[EDMID]
,[Distance]
,[Selected])

SELECT
ASTMID
,'HWG - VT'
,1
,1

FROM dbo.ASTMListCustom

WHERE ASTMID IN ( 15, 21, 22, 23, 25, 38, 63, 72, 73, 74, 75, 82, 83, 125, 130, 163, 165, 182, 206, 207, 208, 214, 217, 250, 255, 256, 257, 264, 266, 299, 317, 342, 348, 349, 350, 357, 381, 382, 391, 392, 397, 398, 422, 448, 450, 451, 466, 481, 9, 12, 17, 18, 26, 61, 62, 67, 68, 69, 70, 77, 85, 92, 93, 94, 95, 126, 128, 129, 136, 137, 145, 146, 153, 154, 179, 203, 211, 213, 219, 221, 237, 253, 254, 261, 262, 301, 326, 327, 328, 329, 343, 346, 353, 368, 369, 386, 394, 436)


I'm Trying to Insert 1 row each ASTMID but it ends up duplicating the number of rows per ASTMID and the duplicates are the same rows you've inserted.

Answer

It appears that your ASTMListCustom table has duplicate rows for the ASTMIDs on your list. You can fix this by adding a GROUP BY to your SELECT:

INSERT INTO dbo.ASTMListCustom
           ([ASTMID]
           ,[EDMID]
           ,[Distance]
           ,[Selected])    
SELECT     ASTMID
           ,'HWG - VT'
           ,1
           ,1
FROM   dbo.ASTMListCustom
WHERE ASTMID IN ( 15, ... )
GROUP BY ASTMID