SQL语句问题(100分)

G

gyj163nt

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-09-24

• #1

表A，表B 结构相同，表B中的部分记录表A中已有，现想将表B中的表A中没有的记录追加到表A中，请问SQL语句如何实现？谢谢！

 

Z

zywcd

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-09-24

• #2

insert into A from B where not (b.id in (select id from a))

 

狼

狼族上将

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-09-24

• #3

假设表的字段有id Name 和Mark
则Insert table1(id, name, mark)
Select id, name, mark From table2
Where Not Exists (Select id From table1 Where id = table2.id and name = table2.name And mark = table2.mark)
假如id是唯一的
可以简化为
Insert table1(id, name, mark)
Select id, name, mark From table2
Where id not in (Select id From table1)

 

C

cis

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-09-24

• #4

insert into a
select b.* from b
left a on b.id = a.id
where a.id is null

 

W

wanderld

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-09-24

• #5

不同的数据库不同的写法
oracle中
insert into a
select * from b where b.XXX not in (select a.xxx from a)

 

X

xiammy

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-09-24

• #6

用连接的方式应该比较效率高！不需要每条记录都判断in操作，而是采用组建索引
同cis
///////////////////////////////////
insert into a
select b.* from b
left a on b.id = a.id
where a.id is null

 

A

alva117

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-09-24

• #7

回复zywcd:
insert into A from B where not (b.id in (select id from a))
是否应该改成:
insert into A select * from B where not (b.id in (select id from a)) ?

 

G

gyj163nt

Unregistered / Unconfirmed

GUEST, unregistred user!

• 2006-09-24

• #8

谢谢大家的热心帮助，狼族上将 的方法可用