去重 存储过程

见如下代码：是多表操作的。

1、存储过程里可以写临时表的，但这里用的是exec语句创建的临时表。同时存储过程里不能写临时表的表名，因为临时表是动态创建的，当时并不存在，所以存储过程会报错。

2、注意到：这里的临时表用完后不用drop，它是自动删除的。

3、exec里面好像只能执行一个命令，就比如说你有很多个select，delete啊那就不行了。所以只能一条一条来。

4、存储过程里不能有go.否则go后面的语句将被抛弃。

5、exex里面执行的语句串是用单引号和加号连接的。

 

create proc [dbo].[clearDupdata] 
@interDay bigint 
as

--table1-----------------------------------------------------------------------------------------------------------------
exec ('select distinct * into #tmpActivity from test.dbo.dActivityCodeStat where 1=2')
exec ('select distinct * into #tmpAgentApp from test.dbo.dAgentByApplicationStat where 1=2')
exec ('select distinct * into #tmpPerform from test.dbo.dAgentPerformanceStat  where 1=2')
exec ('select distinct * into #tmpAppStat from test.dbo.dApplicationStat  where 1=2')
exec ('select distinct * into #tmpSkillsetStat from test.dbo.dSkillsetStat  where 1=2')
exec ('select distinct * into #tmpAgentLogin from test.dbo.eAgentLoginStat where 1=2')
select distinct * into #tmpActivity from test.dbo.dActivityCodeStat where datediff(dd,timestamp,getdate())=@interDay
delete from test.dbo.dActivityCodeStat where datediff(dd,timestamp,getdate())=@interDay
insert into test.dbo.dActivityCodeStat select  * from #tmpActivity


--table2-----------------------------------------------------------------------------------------------------------------


select distinct * into #tmpAgentApp from test.dbo.dAgentByApplicationStat where datediff(dd,timestamp,getdate())=@interDay 
delete from test.dbo.dAgentByApplicationStat where datediff(dd,timestamp,getdate())=@interDay
insert into test.dbo.dAgentByApplicationStat select  * from #tmpAgentApp


--table3-----------------------------------------------------------------------------------------------------------------

select distinct * into #tmpPerform from test.dbo.dAgentPerformanceStat where datediff(dd,timestamp,getdate())=@interDay
delete from test.dbo.dAgentPerformanceStat where datediff(dd,timestamp,getdate())=@interDay
insert into test.dbo.dAgentPerformanceStat select  * from #tmpPerform

--table4-----------------------------------------------------------------------------------------------------------------

select distinct * into #tmpAppStat from test.dbo.dApplicationStat where datediff(dd,timestamp,getdate())=@interDay 
delete from test.dbo.dApplicationStat where datediff(dd,timestamp,getdate())=@interDay
insert into test.dbo.dApplicationStat select  * from #tmpAppStat

--table5-----------------------------------------------------------------------------------------------------------------
select distinct * into #tmpSkillsetStat from test.dbo.dSkillsetStat where datediff(dd,timestamp,getdate())=@interDay 
delete from test.dbo.dSkillsetStat where datediff(dd,timestamp,getdate())=@interDay
insert into test.dbo.dSkillsetStat select  * from #tmpSkillsetStat

--table6-----------------------------------------------------------------------------------------------------------------

select distinct * into #tmpAgentLogin from test.dbo.eAgentLoginStat where datediff(dd,timestamp,getdate())=@interDay 
delete from test.dbo.eAgentLoginStat where datediff(dd,timestamp,getdate())=@interDay
insert into test.dbo.eAgentLoginStat select  * from #tmpAgentLogin

 

 

 

在执行时，用如下命令：

 

 

exec clearDupdata 6

 

refurl:http://topic.csdn.net/t/20021125/12/1202902.html

http://zhidao.baidu.com/question/230958004.html?seed=0

http://topic.csdn.net/u/20080122/16/58dd3f13-652e-4161-b872-3486820bfdaa.html