从其他表中插入数据在sql server中

Question

我在table_3中有一些记录，我想将该记录插入table_2，但第一列（Id）的值将是（NUMBER + 1）table_1。我的代码是

INSERT INTO TABLE_2(Id,NAME) 
SELECT (SELECT MAX(NUMBER) FROM TABLE_1)+1 AS Id,name 
FROM TABLE_3 

TABLE_1计数为200和TABLE_3计数为10.我希望新插入的ID是一样

（201,202,203,204,205,206,207,208,209,210）

但是从查询我越来越像...

（201,201,201,201,201,201,201,201,201,201）

Answer 1

，你可以这样做只是使用ROW_NUMBER()和group by

INSERT INTO TABLE_2(Id,NAME) 
SELECT MAX(NUMBER) + ROW_NUMBER() OVER (ORDER BY name),name 
FROM TABLE_3 
group by name 

only one select

Answer 2

在您的查询(SELECT MAX(NUMBER) FROM TABLE_1)+1始终是相同的，因为MAX(NUMBER)只是200。

如果你想以1为插入每个新记录递增，开始(SELECT MAX(NUMBER) FROM TABLE_1)+1，试试这个：

INSERT INTO TABLE_2 (Id, NAME) 
SELECT Id + rn, name 
FROM (
    SELECT (SELECT MAX(NUMBER) FROM TABLE_1) AS Id, name, 
      ROW_NUMBER() OVER (ORDER BY name) AS rn 
    FROM TABLE_3) t