SQL 如何使用插入选择语句插入下一个最大值
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/21507722/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to insert next max value using insert select statement
提问by Dattatray Kale
I have a two tables. I want to insert table1 data into table2 if records from table1 are not present in the table2.
我有两张桌子。如果 table2 中不存在 table1 中的记录，我想将 table1 数据插入 table2。
I can do this. main problem with my query.
我可以做这个。我的查询的主要问题。
create table #Customer
(
ID int,
Name nvarchar(20),
Email nvarchar(20)
)
create table #Customer2
(
ID int,
Name nvarchar(20),
Email nvarchar(20),
Value varchar(20)
)
insert into #Customer values (1,'AAA','[email protected]')
insert into #Customer values (2,'BBB','[email protected]')
insert into #Customer values (3,'CCC','[email protected]')
insert into #Customer values (4,'DDD','[email protected]')
insert into #Customer values (5,'EEE','[email protected]')
insert into #Customer values (6,'FFF','[email protected]')
insert into #Customer values (7,'GGG','[email protected]')
insert into #Customer2 values (3,'x','[email protected]','10001')
insert into #Customer2 values (6,'y','[email protected]','10002')
insert into #Customer2 values (8,'z','[email protected]','10003')
update C2
set C2.Email = C1.Email, C2.Name = C1.Name
from #Customer C1
inner join #Customer2 C2 on C2.ID = C1.ID
insert into #Customer2
select C1.ID, C1.Name, C1.Email, (SELECT MAX(CONVERT(int, Value))+1 from #Customer2
) from #Customer C1
left join #Customer2 C2 on C2.ID = C1.ID
where C2.ID is null
select ID,value from #Customer2
drop table #Customer
drop table #Customer2
The result is
结果是
id value
id值
3 10001
3 10001
6 10002
6 10002
8 10003
8 10003
1 10004
1 10004
2 10004
2 10004
4 10004
4 10004
5 10004
5 10004
7 10004
7 10004
I want it as
我想要它作为
id value
id值
3 10001
3 10001
6 10002
6 10002
8 10003
8 10003
1 10004
1 10004
2 10005
2 10005
4 10006
4 10006
5 10007
5 10007
7 10008
7 10008
Please advice!
请指教！
Thanking you in anticipation.
感谢你在期待。
采纳答案by A.J
You can use Row_Numberstarting with the maximum value in Customer1table to generate the ID's in customer 2 and the except operator to insert the data which is not present:
您可以使用Row_Number从Customer1表中的最大值开始生成客户 2 中的 ID，并使用 except 运算符插入不存在的数据：
INSERT INTO #Customer2(ID,Name,Email,Value)
select ID,name,email, (SELECT MAX(value) from #customer2) + row_number() over (order by id) value
from (select ID,Name,email from #Customer
except SELECT ID,name,email from #Customer2)ValueForInserting
