Submit My Question - Recent Questions - Recent Answer
Msdn China Tech >> MS-SQL Server >> 疑难问题

这个存储过程怎么写,大家来帮帮忙,谢先了!!!

首先,先往存储过程传递三个参数,表名A,表名B,时间C(其中表A的字段有a,b,c,d,f;表B的字段有a,b,c,d,h)
此存储过程的作用:把表A的所有记录,写入表B,如表B中存在与表A相同的记录,则覆盖表B的记录如:表B.d=表A.d(记录相同条件判断条件是表A.a=表B.a and 表A.b=表B.b and 表A.c=表B.c),同时将时间写入表B.h字段.
两个表的记录比较多，哪位大虾有最好的写法，请赐教，谢先!
[237 byte] By [zero2002-呵呵] at [2008-5-24]
«« 请问如何编写存储过程实现两个服务器之间的指定表的数据导入或导出?
»» 请问TreeView控件Node的拖动问题.
# 1
create proc proctest
@A varchar(50),
@B varchar(50),
@C datetime
as

declare @CC varchar(30)
set @CC=convert(varchar(30),@C,120)
declare @SQL varchar(2000)

set @SQL='update '+@B +' set d=a.d,h='''+@CC+''' from '+@A+' a,'+@B+' b where a.a=b.a and a.b=b.b and a.c=b.c'

exec (@sql)

set @SQL='insert '+@B+'(a,b,c,d,h) select a,b,c,d,'''+@CC+''' from '+@A+' a where not exists ( select * from '+@B+' b where a.a=b.a and a.b=b.b and a.c=b.c)'

exec (@sql)

go
CSDNM-决定不当CSDN经理了 at 2007-10-24 > top of Msdn China Tech,MS-SQL Server,疑难问题...
# 2
create proc proc1(@A varchar(20),@B varchar(20),@c
datetime)
as
begin
declare @sql nvarchar(4000)

set @sql='delete '+@B+' from '+@A+','+@B+
' where '+@A+'.a='+@B+'.a and '+@A+'.b='+@B+'.b and
'+@A+'.c='+@B+'.c'

exec(@sql)

set @Sql='insert '+@B+'(a,b,c,d,h) select a,b,c,d,'''
+convert(varchar(30),@c,120)+''' from '+@A

exec(@sql)

end
j9988-j9988 at 2007-10-24 > top of Msdn China Tech,MS-SQL Server,疑难问题...
# 3
功能是实现,但不是很完善,对ERROR的处理,事务的处理,我修改了一下:
create proc proctest
@A varchar(50),
@B varchar(50),
@C datetime
as

declare @CC varchar(30)
set @CC=convert(varchar(30),@C,120)
declare @SQL varchar(2000)

declare @rc int
select @rc=0
if @rc=0
begin transaction up_table
begin
set @SQL='update '+@B +' set d=a.d,h='''+@CC+''' from '+@A+' a,'+@B+' b where a.a=b.a and a.b=b.b and a.c=b.c'
exec (@sql)
select @rc=@@error
if @rc<>0
select @rc=-1
return @rc
end

begin
set @SQL='insert '+@B+'(a,b,c,d,h) select a,b,c,d,'''+@CC+''' from '+@A+' a where not exists ( select * from '+@B+' b where a.a=b.a and a.b=b.b and a.c=b.c)'
exec (@sql)
select @rc=@@error
if @rc<>0
select @rc=-2
return @rc
end
if @rc=0
commit transaction up_table
else
rollback transaction up_table

go

leimin-黄山光明顶 at 2007-10-24 > top of Msdn China Tech,MS-SQL Server,疑难问题...
# 4
先把表B中与表A相同的字段删除，再用select ..Insert把整个表A插入即可。
Rodgu-棒子 at 2007-10-24 > top of Msdn China Tech,MS-SQL Server,疑难问题...
# 5
create proc proctest
@A varchar(50),
@B varchar(50),
@C datetime
as

declare @CC varchar(30)
set @CC=convert(varchar(30),@C,120)
declare @SQL varchar(2000)

Select @ExecStr='Delete From '+@B+'B left join '+@A+'A on B.a=A.a and B.b=A.b and B.c=A.c'
Exec(@Execstr)
Selet @ExecStr='insert '+@B+'(a,b,c,d,h) select a,b,c,d,'''
+convert(varchar(30),@c,120)+''' from '+@A
Exec(@ExecStr)
forgot-忘记forgot2000 at 2007-10-24 > top of Msdn China Tech,MS-SQL Server,疑难问题...
# 6
学习，学习…
xwmhn-小猫 at 2007-10-24 > top of Msdn China Tech,MS-SQL Server,疑难问题...
# 7
先删后插，大家都已经做得很好了，但是最好是放在一个事务中去。
bittcn-OceanWave at 2007-10-24 > top of Msdn China Tech,MS-SQL Server,疑难问题...
MS-SQL Server
疑难问题

MS-SQL Server Hot Topic

MS-SQL Server New Topic

MS-SQL Server

Java / MSDN / Linux Tech / PHP Tech / MSDN DotNet / MSDN TECH / Health Library / Programmers Tech / visual basic Tech / Developer Tech / Ipod faqs / Msdn China Tech

Site Classified