此存储过程思路很简单,就是将EAS001库里的某些表,每天定时insert到EAS002库里的同名表里(2个库的表结构一致),如果EAS002库里有数据,先将之删除,再插入(相当于upadte);没有则直接插入。现在只写了一个表的(t_bd_materialInventory),写死了。想做得灵活些,但发现把表名作为参数变量,不能直接执行sql,要在exec(@sql)执行。但是我又用到了游标。求高手指点USE [eas001]
GO
/****** 对象: StoredProcedure [dbo].[pro_bd_materialInventory] 脚本日期: 04/11/2011 11:49:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[pro_bd_materialInventory]
@flastUpdateTime datetime,
@destTable varchar(50)
as
begin
DECLARE @source_fid nvarchar(100)
DECLARE @COUNT int
DECLARE abc CURSOR FOR
SELECT fid FROM eas001.dbo.t_bd_materialInventory where flastUpdateTime > @flastUpdateTime
OPEN abc
FETCH NEXT FROM abc into @source_fid /*源表fid*/
while @@FETCH_STATUS = 0
begin
select @count=count(*) from eas002.dbo.t_bd_materialInventory where fid = @source_fid
if @count>0
begin
delete eas002.dbo.t_bd_materialInventory where fid = @source_fid
end
insert into eas002.dbo.t_bd_materialInventory select * from eas001.dbo.t_bd_materialInventory a where a.fid = @source_fid
FETCH NEXT FROM abc into @source_fid
end
CLOSE abc
DEALLOCATE abc
end
GO
/****** 对象: StoredProcedure [dbo].[pro_bd_materialInventory] 脚本日期: 04/11/2011 11:49:24 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER proc [dbo].[pro_bd_materialInventory]
@flastUpdateTime datetime,
@destTable varchar(50)
as
begin
DECLARE @source_fid nvarchar(100)
DECLARE @COUNT int
DECLARE abc CURSOR FOR
SELECT fid FROM eas001.dbo.t_bd_materialInventory where flastUpdateTime > @flastUpdateTime
OPEN abc
FETCH NEXT FROM abc into @source_fid /*源表fid*/
while @@FETCH_STATUS = 0
begin
select @count=count(*) from eas002.dbo.t_bd_materialInventory where fid = @source_fid
if @count>0
begin
delete eas002.dbo.t_bd_materialInventory where fid = @source_fid
end
insert into eas002.dbo.t_bd_materialInventory select * from eas001.dbo.t_bd_materialInventory a where a.fid = @source_fid
FETCH NEXT FROM abc into @source_fid
end
CLOSE abc
DEALLOCATE abc
end
--原来的表
declare @a table (id int,col int)
insert into @a
select 1,1 union all
select 2,2 union all
select 3,1 union all
select 4,1--新来的表
declare @b table (id int,col int)
insert into @b
select 2,4 union all
select 3,4 union all
select 5,4--批量删除
DELETE @a FROM @a a LEFT JOIN @b b ON a.id=b.id
WHERE b.id IS NOT NULL--批量插入
INSERT INTO @a SELECT * FROM @b--处理后的结果
SELECT * FROM @a
/*
id col
----------- -----------
1 1
2 4
3 4
4 1
5 4
*/
如果要把表名作为参数,那就是用动态SQL语句。
Create procedure Pro_bd_masterialInventory
as
Begin
insert into
eas002.dbo.t_bd_materialInventory
select * from eas001.dbo.t_bd_materialInventory a where not exists(select fid from eas002.dbo.t_bd_materialInventory b where a.fid =b.fid )
End