select Id , Pid into #tmp1 from table2 (nolock) delete a from #tmp1 a, table1 b where a.id=b.id and a.pid=b.pidinsert into table1 select Id , Pid from #tmp1
看来table1有值了 如果id或pid其一是主键的话insert into table1 select Id , Pid from table 2 where id not in (select id from table1) 若没有主键的话, insert into table1 select a.Id , a.Pid from table2 a, table1 b where a.id<>b.id and a.pid<>b.pid
-- 以下語句也可以insert into table1 select Id , Pid from table2 where id not in (select id from table1 ) and pid not in (select pid from table2)
insert into table1 select a.Id,a.Pid from table2 left join table1 b on a.id=b.id and a.pid=b.pid where b.id is null and b.pid is null
insert into table1 select id,Pid from table2 where cast(id as varchar(10))+','+cast(pid as varchar(10)) not in (select cast(id as varchar(10))+','+cast(pid as varchar(10)) from table1)
使記錄不重復有多種方法,參見下面代碼,做點修改即可。 --刪除重復列 a.如果有ID字段,就是具有唯一性的字段delect table where id not in (select max(id) from table group by col1,col2,col3... ) group by 子句后跟的字段就是你用到判斷重复的字段b.,如果是判斷所有字段 select * into #aa from table group by id1,id2,.... delete table table insert into table select * from #aac.如果表中有ID的情況select identity(int,1,1) as id,* into #temp from tabel delect # where id not in ( select max(id) from # group by col1,col2,col3...) delect table inset into table(...) select ..... from #temp col1+','+col2+','...col5 組合主鍵 select * from table where col1+','+col2+','...col5 in (select max(col1+','+col2+','...col5) from table where having count(*)>1 group by col1,col2,col3,col4 ) group by 子句后跟的字段就是你用到判斷重复的字段d. select identity(int,1,1) as id,* into #temp from tabel select * from #temp where id in ( select max(id) from #emp where having count(*)>1 group by col1,col2,col3...)e. alter table yourtable add rownum int identity(1,1) go delete from yourtable where rownum not in (select min(rownum ) from yourtable group by 你重复的字段名) go alter table yourtable drop column rownum gof. alter table 表 add newfield int identity(1,1) delete 表 where newfield not in( select min(newfield) from 表 group by 除newfield外的所有字段 )alter table 表 drop column newfield g. -- 刪除表中重復的記錄 DELETE delete1 FROM tabTest delete1 JOIN tabTest delete2 ON delete1.student_id=delete2.student_id AND delete1.course_id=delete2.course_id AND delete1.id>delete2.id
insert into table1 select distinct Id , Pid from table 2 where ... 用這個就行了
delete a
from #tmp1 a, table1 b
where a.id=b.id
and a.pid=b.pidinsert into table1 select Id , Pid from #tmp1
如果id或pid其一是主键的话insert into table1
select Id , Pid from table 2
where id not in (select id from table1)
若没有主键的话,
insert into table1
select a.Id , a.Pid from table2 a, table1 b
where a.id<>b.id and a.pid<>b.pid
select Id , Pid from table2
where id not in (select id from table1 )
and pid not in (select pid from table2)
select a.Id,a.Pid from table2 left join table1 b on a.id=b.id and a.pid=b.pid
where b.id is null and b.pid is null
select id,Pid from table2
where cast(id as varchar(10))+','+cast(pid as varchar(10)) not in
(select cast(id as varchar(10))+','+cast(pid as varchar(10)) from table1)
--刪除重復列
a.如果有ID字段,就是具有唯一性的字段delect table where id not in (select max(id) from table group by col1,col2,col3...
)
group by 子句后跟的字段就是你用到判斷重复的字段b.,如果是判斷所有字段
select * into #aa from table group by id1,id2,....
delete table table
insert into table
select * from #aac.如果表中有ID的情況select identity(int,1,1) as id,* into #temp from tabel
delect # where id not in (
select max(id) from # group by col1,col2,col3...)
delect table
inset into table(...)
select ..... from #temp
col1+','+col2+','...col5 組合主鍵
select * from table where col1+','+col2+','...col5 in (select max(col1+','+col2+','...col5) from table
where having count(*)>1
group by col1,col2,col3,col4
)
group by 子句后跟的字段就是你用到判斷重复的字段d.
select identity(int,1,1) as id,* into #temp from tabel
select * from #temp where id in (
select max(id) from #emp where having count(*)>1 group by col1,col2,col3...)e.
alter table yourtable add rownum int identity(1,1)
go
delete from yourtable where rownum not in (select min(rownum ) from yourtable group by 你重复的字段名)
go
alter table yourtable drop column rownum
gof.
alter table 表 add newfield int identity(1,1)
delete 表
where newfield not in(
select min(newfield) from 表 group by 除newfield外的所有字段
)alter table 表 drop column newfield
g.
-- 刪除表中重復的記錄
DELETE delete1
FROM tabTest delete1
JOIN tabTest delete2
ON delete1.student_id=delete2.student_id AND delete1.course_id=delete2.course_id AND delete1.id>delete2.id
select distinct Id , Pid from table 2
where ...
用這個就行了