请求高人帮忙,对我来说真的很难的一存储过程,急.......

[sf8989]

两个表A(AA,BB,CC,DD),B(AA,BB,CC,DD),两个表的结构完全一样,字段AA为主键

现在要查出表A中BB字段的值等于某一个数据(@BB)的数据集,再将这个数据集插入到表B中,

而当插入的时候又要检查表B中是否有这一行数据(以主键AA为准),有的时候就更新,没有的时候就插入.



求一个存储过程的写法,如那位高人会做的,能不能尽量给出原代码,如不能,多给点提示也好.
非常感谢!!

[phantomMan]

declare @bb varchar(10)
if not exists(select * from a where a.aa=(select aa from b where b.bb=@bb))
insert into bb select * from a where a.bb=@bb
else
update b set bb=(select bb from a where a.bb=@bb),
cc=(select cc from a where a.bb=@bb),
dd=(select dd from a where a.bb=@bb)

[Soundboy]

用Sql能实现吗

[520zyb]

if not exists(select * from B,A where B.AA=A.AA and A.bb=@bb)
insert into bb select * from a where a.bb=@bb
else
update b set bb=(select bb from a where a.bb=@bb),
cc=(select cc from a where a.bb=@bb),
dd=(select dd from a where a.bb=@bb)

[520zyb]

修改:
if not exists(select * from B,A where B.AA=A.AA and A.bb=@bb)
insert into bb select * from a where a.bb=@bb
else
update B set bb= A.bb,
cc= A.cc,
dd= A.dd
from A,B where A.bb=@bb and A.aa=B.aa

[子陌红尘]

create procedure sp_test(@BB int)
as
begin
if exists(select 1 from A,B where A.AA=B.AA and A.BB=@BB)
update B
set
BB=A.BB,CC=A.CC,DD=A.DD
from
A,B
where
A.AA=B.AA and A.BB=@BB
else
insert into B select * from A where BB=@BB
end
go

[bugchen888]

楼上正解.

[funsuzhou]

CREATE PROCEDURE p1 AS
declare c1 cursor local for
select AA from 表A where BB=你要查的值
declare @AA varchar(25)
declare @i int
Open c1
Fetch next from c1 into @AA
while @@Fetch_Status=0
begin
select @i=count(*)from 表B where AA=@AA
if@i=0
begin
insert into 表B select * from 表A where AA=@AA
end
else
begin
update 表B set BB=t.BB,CC=t.CC,DD=t.DD from 表A t inner join 表B u on t.AA=u.AA
end
end
Close c1
Deallocate c1
GO

[funsuzhou]

漏了一句Fetch next from c1 into @AA

CREATE PROCEDURE p1 AS
declare c1 cursor local for
select AA from 表A where BB=你要查的值
declare @AA varchar(25)
declare @i int
Open c1
Fetch next from c1 into @AA
while @@Fetch_Status=0
begin
select @i=count(*)from 表B where AA=@AA
if@i=0
begin
insert into 表B select * from 表A where AA=@AA
end
else
begin
update 表B set BB=t.BB,CC=t.CC,DD=t.DD from 表A t inner join 表B u on t.AA=u.AA
end
Fetch next from c1 into @AA
end
Close c1
Deallocate c1
GO