copy from username/password@dbname to username1/pw1@dbname1 append(create/insert/replace) tablename(col1,col2...) using select....from ...; append:添加到表上,如果tablename表不存在,则建立此表 create:如果tablename表存在,则出错 insert:添加到表中,如果tablename表不存在,则出错 replace:替换表中数据,如果tablename表不存在,则建立此表.
delete from ( insert into a select * from b) group by 所有字段 having count(*) > 1不知道能不能,没试,自己这么想的.请大家批评!
如果没有主键可以用 insert into a select * from b minus select * from a如果有主键 insert into a select (select max(id) from a) + rownum,name from ( select id,name from b minus select id,name from a ) t
3种办法 1, insert into a select * from b minus select * from a2, insert into a select * from b where not exists (select 1 from a.id = b.id )3,mergemerge into T1 using T on (T1.C1 = T.C1) when matched then update set T1.C2 = T.C2 when not matched then insert values (T.C1, T.C2); end;4 ,copy
我也刚学,下面是我这两天找到的,向一个表插,如果存在则删除,否则插,也许对你有参考价值 [Q]对一个表如果存在就更新,不存在就插入可以用一个语句实现吗 [A]9i已经支持了,是Merge,但是只支持select子查询, 如果是单条数据记录,可以写作select …… from dual的子查询。 语法为: MERGE INTO table USING data_source ON (condition) WHEN MATCHED THEN update_clause WHEN NOT MATCHED THEN insert_clause; 如 MERGE INTO course c USING (SELECT course_name, period, course_hours FROM course_updates) cu ON (c.course_name = cu.course_name AND c.period = cu.period) WHEN MATCHED THEN UPDATE SET c.course_hours = cu.course_hours WHEN NOT MATCHED THEN INSERT (c.course_name, c.period, c.course_hours) valueS (cu.course_name, cu.period, cu.course_hours);
to username1/pw1@dbname1
append(create/insert/replace) tablename(col1,col2...)
using select....from ...;
append:添加到表上,如果tablename表不存在,则建立此表
create:如果tablename表存在,则出错
insert:添加到表中,如果tablename表不存在,则出错
replace:替换表中数据,如果tablename表不存在,则建立此表.
-----------------------------------------
有点问题,如果b表有重复纪录,这句话意思是a表的纪录在b表存在,还是b表原本就存在2条相同的纪录??还有如果B中有重复纪录则删除该纪录,否则追加,这句话更不好理解了,楼主先把问题描述清楚,说不明白也给个示例啊
或者参考2楼的运用copy命令来做
(
insert into a
select * from b)
group by 所有字段
having count(*) > 1不知道能不能,没试,自己这么想的.请大家批评!
insert into a
select * from b
minus
select * from a如果有主键
insert into a
select (select max(id) from a) + rownum,name
from (
select id,name from b
minus
select id,name from a
) t
1,
insert into a
select * from b
minus
select * from a2,
insert into a
select * from b
where not exists
(select 1
from a.id = b.id
)3,mergemerge into T1
using T
on (T1.C1 = T.C1)
when matched then
update
set T1.C2 = T.C2
when not matched then
insert
values
(T.C1, T.C2);
end;4 ,copy
[Q]对一个表如果存在就更新,不存在就插入可以用一个语句实现吗
[A]9i已经支持了,是Merge,但是只支持select子查询,
如果是单条数据记录,可以写作select …… from dual的子查询。
语法为:
MERGE INTO table
USING data_source
ON (condition)
WHEN MATCHED THEN update_clause
WHEN NOT MATCHED THEN insert_clause;
如
MERGE INTO course c
USING (SELECT course_name, period,
course_hours
FROM course_updates) cu
ON (c.course_name = cu.course_name
AND c.period = cu.period)
WHEN MATCHED THEN
UPDATE
SET c.course_hours = cu.course_hours
WHEN NOT MATCHED THEN
INSERT (c.course_name, c.period,
c.course_hours)
valueS (cu.course_name, cu.period,
cu.course_hours);