你这句写错了哈:INSERT INTO tb1 (ID,col1,col2) select MAX(tb1.ID)+1,col1,col2 from tb2改为:INSERT INTO tb1 (ID,col1,col2) select MAX(tb2.ID)+1,col1,col2 from tb2
create table tb1 (ID int,col1 varchar(5),col2 varchar(5))create table tb2 (col1 varchar(5),col2 varchar(5))insert into tb1(ID,col1,col2) select 1,'aa','bb' union all select 2,'cc','dd' union all select 3,'ee','ff'insert into tb2(col1,col2) select 'gg','hh' union all select 'ii','jj' insert into tb1(ID,col1,col2) select isnull((select max(ID) from tb1),0) +row_number() over(order by getdate()),col1,col2 from tb2select ID,col1,col2 from tb1/* ID col1 col2 ----------- ----- ----- 1 aa bb 2 cc dd 3 ee ff 4 gg hh 5 ii jj(5 row(s) affected) */
2005以上版本 insert into tb1(id,col1,col2) select (select max(id) from tb1)+rn,col1,col2 from (select *,rn=row_number() over (order by getdate()) from tb2)a
create table tb1
(ID int,col1 varchar(5),col2 varchar(5))create table tb2
(col1 varchar(5),col2 varchar(5))insert into tb1(ID,col1,col2)
select 1,'aa','bb' union all
select 2,'cc','dd' union all
select 3,'ee','ff'insert into tb2(col1,col2)
select 'gg','hh' union all
select 'ii','jj'
insert into tb1(ID,col1,col2)
select isnull((select max(ID) from tb1),0)
+row_number() over(order by getdate()),col1,col2
from tb2select ID,col1,col2 from tb1/*
ID col1 col2
----------- ----- -----
1 aa bb
2 cc dd
3 ee ff
4 gg hh
5 ii jj(5 row(s) affected)
*/
insert into tb1(id,col1,col2)
select (select max(id) from tb1)+rn,col1,col2 from
(select *,rn=row_number() over (order by getdate()) from tb2)a
就不需要每次insert的时候找最大值了