INSERT INTO与WHERE IN子句

Question

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) 

我想插入一行每个ASTMID，但它结束了复制每ASTMID的行数和重复的是您插入相同的行。

Answer 1

看起来您的ASTMListCustom表在您的列表中有ASTMID s的重复行。您可以通过添加一个GROUP BY您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