-- 创建临时表 create table #a( i int primary key, c1 varchar(20), c2 varchar(20) ) insert into #a select 1,'x1','y1' union all select 2,'x2','y2' union all select 3,'x3','y3' union all select 4,'x4','y4' union all select 5,'x5','y5'
create table #b( i int primary key, c1 varchar(20), c2 varchar(20) )
insert into #b select 3,'x3','y3' union all select 5,'x5','y5' -- 插入数据 insert into #b select * from #a where not exists(select '' from #b where #a.i = #b.i) -- 显示并清理 select * from #b drop table #a drop table #b
insert into B select * from A
insert bselect * from a where b.index<>a.index
oracle 的 insert into B select index,*,*... from A where not B.index<>A.index;
insert into B select * from A where not exists(select * from B where A.i = B.i
drop table B; create table B as select * from A;
吃不消了。如果是Oracle的话,为什么不用merge呢?
insert into B select * from A
哦,对了,这种插法是不能有自增列的 就是identify
其实如果B表没有建立,有更简单的办法 ====================================== select * into B from A
你还要认为控制下数据不重复的 不然就加条件 where not exist(条件)
比如 t1 表 a name age 1 a 10 2 b 15 t2表a name age 1 a 10 2 c 13那执行SQL后 我的结果 a name age 1 a 10 2 c 13 3 b 15
楼主【dreamhyz】截止到2008-07-17 23:45:24的历史汇总数据(不包括此帖):
发帖的总数量:56 发帖的总分数:2377 每贴平均分数:42
回帖的总数量:417 得分贴总数量:160 回帖的得分率:38%
结贴的总数量:56 结贴的总分数:2377
无满意结贴数:7 无满意结贴分:250
未结的帖子数:0 未结的总分数:0
结贴的百分比:100.00% 结分的百分比:100.00%
无满意结贴率:12.50 % 无满意结分率:10.52 %
敬礼!
-- 创建临时表
create table #a(
i int primary key,
c1 varchar(20),
c2 varchar(20)
)
insert into #a
select 1,'x1','y1' union all
select 2,'x2','y2' union all
select 3,'x3','y3' union all
select 4,'x4','y4' union all
select 5,'x5','y5'
create table #b(
i int primary key,
c1 varchar(20),
c2 varchar(20)
)
insert into #b
select 3,'x3','y3' union all
select 5,'x5','y5'
-- 插入数据
insert into #b
select * from #a
where not exists(select '' from #b where #a.i = #b.i)
-- 显示并清理
select * from #b
drop table #a
drop table #b
where b.index<>a.index
insert into B select index,*,*... from A where not B.index<>A.index;
select * from A
where not exists(select * from B where A.i = B.i
create table B as select * from A;
就是identify
======================================
select * into B from A
不然就加条件
where not exist(条件)
t1 表 a name age
1 a 10
2 b 15
t2表a name age
1 a 10
2 c 13那执行SQL后
我的结果
a name age
1 a 10
2 c 13
3 b 15