-- 导入不重复的记录 insert into tbB select * from tbA a where not exists (select 1 from tbA b where b.thefield=a.thefield and b.id!=a.id)-- 重复记录导入临时表 select ... ,thefield = thefield + char(65+(select count(1) from tbA b0 where b0.thefield=a.thefield and b0.id<a.id)) -- 序号 A,B,C.. ,... into #tmp_tab from tbA a where exists (select 1 from tbA b where b.thefield=a.thefield and b.id!=a.id)
'借楼上思路,,' create table ax (a int PRIMARY KEY , b int, c int) create table bx (a int, b int, c int)insert into bx values (11,12,13) insert into bx values (11,15,16) insert into bx values (11,16,16) insert into bx values (12,15,16) insert into bx values (12,16,16) insert into bx values (13,15,16)select convert(varchar(20),a)+char(64+(select top 1 count(1) from bx where t.a=a and t.b>=b ))a ,b,c from bx t where a not in (select a from bx group by a having(count(*))=1) /* 11A 12 13 11B 15 16 11C 16 16 12A 15 16 12B 16 16*/
表A插入主键 id int identity(1,1) select (select count(id) from 表A as a where b.a=a.a and a.id>=b.id),* from 表A as b
insert into b select a.* from a where not exists(select 1 from b where a.id=b.id)
insert into b select a.* from a where not exists(select 1 from b where a.id=b.id) 这个不行,我适过了.有出错信息.违反了主码约束
insert into tbB
select *
from tbA a
where not exists (select 1 from tbA b where b.thefield=a.thefield and b.id!=a.id)-- 重复记录导入临时表
select
...
,thefield = thefield + char(65+(select count(1) from tbA b0 where b0.thefield=a.thefield and b0.id<a.id))
-- 序号 A,B,C..
,...
into #tmp_tab
from tbA a
where exists (select 1 from tbA b where b.thefield=a.thefield and b.id!=a.id)
'借楼上思路,,'
create table ax (a int PRIMARY KEY , b int, c int)
create table bx (a int, b int, c int)insert into bx values (11,12,13)
insert into bx values (11,15,16)
insert into bx values (11,16,16)
insert into bx values (12,15,16)
insert into bx values (12,16,16)
insert into bx values (13,15,16)select convert(varchar(20),a)+char(64+(select top 1 count(1) from bx where t.a=a and t.b>=b ))a ,b,c
from bx t where a not in (select a from bx group by a having(count(*))=1)
/*
11A 12 13
11B 15 16
11C 16 16
12A 15 16
12B 16 16*/
select (select count(id) from 表A as a where b.a=a.a and a.id>=b.id),* from 表A as b
insert into b
select a.* from a
where not exists(select 1 from b where a.id=b.id)
select a.* from a
where not exists(select 1 from b where a.id=b.id)
这个不行,我适过了.有出错信息.违反了主码约束