把b加约束 alter table a add constraint 唯一约束a unique(id)
create table a(col1 char(10), col2 char(10)) create table b(id int, col1 char(10), col2 char(10))insert a select 'a', '1' insert b select 1, 'b', '2' insert b select 2, 'c', '3' insert b select 3, 'd', '4'gocreate table #(id int identity(1,1), col1 char(10), col2 char(10))SET IDENTITY_INSERT # ON insert #(id, col1, col2) select id, col1, col2 from b SET IDENTITY_INSERT # OFF insert #(col1, col2) select col1, col2 from a where not exists(select 1 from b where a.col1 = b.col1 and a.col2 = b.col2)insert b(id, col1, col2) select id, col1, col2 from # where not exists(select 1 from b where b.id = #.id) select * from b /* id col1 col2 ----------- ---------- ---------- 1 b 2 2 c 3 3 d 4 4 a 1 */
这个是什么意思?
直接这样不行?insert into B(id,col1,col2)
select row_number() over(order by newid()),col1,col2 from A
这个是什么意思?
直接这样不行?insert into B(id,col1,col2)
select row_number() over(order by newid()),col1,col2 from A
b表中需要id 并且没有设id自增。
我的row_number()函数在我的数据库上跑不起来。。要求id必须是"1234567"这种只含有数组的varchar型。并且按你这种写法生成id的函数应该只会执行一次,就产生了5楼说的情况
把b加约束
alter table a add constraint 唯一约束a unique(id)
create table b(id int, col1 char(10), col2 char(10))insert a select 'a', '1'
insert b select 1, 'b', '2'
insert b select 2, 'c', '3'
insert b select 3, 'd', '4'gocreate table #(id int identity(1,1), col1 char(10), col2 char(10))SET IDENTITY_INSERT # ON
insert #(id, col1, col2)
select id, col1, col2 from b
SET IDENTITY_INSERT # OFF
insert #(col1, col2)
select col1, col2 from a where not exists(select 1 from b where a.col1 = b.col1 and a.col2 = b.col2)insert b(id, col1, col2)
select id, col1, col2 from # where not exists(select 1 from b where b.id = #.id)
select * from b
/*
id col1 col2
----------- ---------- ----------
1 b 2
2 c 3
3 d 4
4 a 1
*/