第一次写这么长的存储过程，存下来记录下

原创 于 2012-07-18 15:57:38 发布 · 471 阅读 · 0 ·
CC 4.0 BY-SA版权
版权声明：本文为博主原创文章，遵循 CC 4.0 BY-SA 版权协议，转载请附上原文出处链接和本声明。
文章标签：

#存储 #date #sql #insert #output #object

本文深入探讨了信息技术领域的核心资源及其应用，包括但不限于前端开发、后端开发、移动开发、游戏开发、大数据开发、AI音视频处理等多个细分领域。文章详细介绍了各领域的关键技术和实践案例，旨在为读者提供全面的技术洞察和应用指导。 
USE [NIR]
GO
/****** Object:  StoredProcedure [dbo].[DBTools_NIR_MoveOldData]    Script Date: 2012/8/1 8:54:35 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
/*------------------------------------
-- 用途： 
-- 项目名称： 
-- 说明：
-- 时间： 
-- 编写者: 
--------------------------------------
-- 修改记录:
-- 编号	修改时间		修改人		修改原因		修改标注
------------------------------------*/
/*	测试语句
DECLARE @Return INT
EXEC DBTools_NIR_MoveOldData @StartTime='2000-01-01',@EndTime='2000-01-31',@Return=@Return OUTPUT
SELECT @Return
*/
ALTER PROCEDURE [dbo].[DBTools_NIR_MoveOldData]
(
	@StartTime DATETIME,
	@EndTime DATETIME,
	@Return INT OUTPUT,--@FunctionType=1时 @Return=1代表没有分库数据，@Return=2代表存在分库数据@FunctionType=2时 @Return=1代表分库成功，@Return=-1代表分库失败
	@FunctionType INT=1 --1表示测试是否有分库数据，2表示执行分库

)
AS
	BEGIN
		SET NOCOUNT ON 
		DECLARE @SQL NVARCHAR(MAX)
		SET @Return=1

		--建表记录需要处理的表名称,表记录数等信息
		CREATE TABLE #TEMP
		(
			TableName  NVARCHAR(50),
			RecCount INT,
			PreFix NVARCHAR(30),
			Suffix NVARCHAR(10)
		)

		CREATE TABLE #TEMP4 (DatabaseName NVARCHAR(20))
		--建立表记录日志库转存前目标表的记录数
		CREATE TABLE #TEMP5
		(
			DatabaseName NVARCHAR(20),
			TableName NVARCHAR(100),
			RecCount INT
		)
		INSERT INTO #TEMP4
			   SELECT name 
						FROM SYS.databases 
						WHERE name LIKE 'NIRLOG______'
						AND RIGHT(name,6)>=LEFT(CONVERT(NVARCHAR(8),@StartTime,112),6)
						AND RIGHT(name,6)<=LEFT(CONVERT(NVARCHAR(8),@EndTime,112),6)
		
		DECLARE @DatabaseName NVARCHAR(20)
		DECLARE TEMP4_CURSOR CURSOR
				FOR SELECT DatabaseName 
							FROM #TEMP4  
		OPEN  TEMP4_CURSOR 
		FETCH NEXT FROM TEMP4_CURSOR INTO @DatabaseName
		
		WHILE @@FETCH_STATUS=0
		BEGIN
			SET @SQL=N'
						USE '+@DatabaseName+'    
						INSERT INTO #TEMP5
						SELECT  '''+@DatabaseName+''' AS DatabaseName,
								T1.name AS TableName,
								T2.rows AS RecCount
									FROM SYS.TABLES T1
									INNER JOIN (SELECT OBJECT_NAME(id) AS name,rows ,indid 
																	FROM sysindexes 
																	WHERE indid<2) T2
									ON T1.name=T2.name
									WHERE REVERSE(SUBSTRING(REVERSE(T1.name),1,LEN(T1.name)-CHARINDEX(''2'',T1.name)+1))>='''+CONVERT(NVARCHAR(8),@StartTime,112)+'''
										AND REVERSE(SUBSTRING(REVERSE(T1.name),1,LEN(T1.name)-CHARINDEX(''2'',T1.name)+1))<='''+CONVERT(NVARCHAR(8),@EndTime,112)+'''
										AND (T1.name like ''NIR_log_ChatFetion________''
										OR T1.name like ''NIR_log_BBS________''
										OR T1.name like ''NIR_log_ChatFetionGroup________''
										OR T1.name like ''NIR_log_ChatMSN________''
										OR T1.name like ''NIR_log_ChatMSNGroup________''
										OR T1.name like ''NIR_log_ChatQQ________''
										OR T1.name like ''NIR_log_ChatQQGroup________''
										OR T1.name like ''NIR_log_ChatUC________''
										OR T1.name like ''NIR_log_ChatUCGroup________''
										OR T1.name like ''NIR_log_ChatYahoo________''
										OR T1.name like ''NIR_log_ChatYahooGroup________''
										OR T1.name like ''NIR_log_SearchKeyword________''
										OR T1.name like ''NIR_log_CyberLogin________''
										OR T1.name like ''NIR_re_ClientCyber________'')

						'
			EXEC(@SQL)			    
			FETCH NEXT FROM TEMP4_CURSOR INTO @DatabaseName
		END 
		CLOSE TEMP4_CURSOR
		DEALLOCATE TEMP4_CURSOR


		--查询需要处理的表对应的记录数、日期等信息
		INSERT INTO #TEMP
		SELECT  T1.name,
				T2.rows,
				SUBSTRING(T1.name,1,CHARINDEX('2',T1.name)-1),
				REVERSE(SUBSTRING(REVERSE(T1.name),1,LEN(T1.name)-CHARINDEX('2',T1.name)+1))

					FROM SYS.TABLES T1
					INNER JOIN (SELECT OBJECT_NAME(id) AS name,rows  
													FROM sysindexes 
													WHERE indid<2) T2
					ON T1.name=T2.name
					WHERE REVERSE(SUBSTRING(REVERSE(T1.name),1,LEN(T1.name)-CHARINDEX('2',T1.name)+1))>=CONVERT(NVARCHAR(8),@StartTime,112)
					 AND REVERSE(SUBSTRING(REVERSE(T1.name),1,LEN(T1.name)-CHARINDEX('2',T1.name)+1))<=CONVERT(NVARCHAR(8),@EndTime,112)
					 AND (T1.name like 'NIR_log_ChatFetion________'
					   OR T1.name like 'NIR_log_BBS________'
					   OR T1.name like 'NIR_log_ChatFetionGroup________'
					   OR T1.name like 'NIR_log_ChatMSN________'
					   OR T1.name like 'NIR_log_ChatMSNGroup________'
					   OR T1.name like 'NIR_log_ChatQQ________'
					   OR T1.name like 'NIR_log_ChatQQGroup________'
					   OR T1.name like 'NIR_log_ChatUC________'
					   OR T1.name like 'NIR_log_ChatUCGroup________'
					   OR T1.name like 'NIR_log_ChatYahoo________'
					   OR T1.name like 'NIR_log_ChatYahooGroup________')
					 order by name
		
		--更新目标表的记录数，加上原来分库中的表的已存在的记录数，便于后面跟转存后的数据想比较
		UPDATE T1
		SET T1.RecCount=T1.RecCount+T2.RecCount
		FROM #TEMP T1
		INNER JOIN #TEMP5 T2
		ON T1.TableName = T2.TableName      

		IF EXISTS (SELECT 1
							FROM #TEMP)
		BEGIN  
			SET @Return=2
			IF @FunctionType=2
			BEGIN    
				--将目标表中的数据逐表复制到分库中
				DECLARE TEMP1_CURSOR CURSOR FOR 
						SELECT TableName,PreFix,Suffix
									FROM #TEMP

				DECLARE @TableName NVARCHAR(50),
						@PreFix NVARCHAR(30),
						@Suffix NVARCHAR(10),
						@ResultCount INT       --转存后表的记录数

				OPEN TEMP1_CURSOR
				FETCH NEXT FROM TEMP1_CURSOR INTO @TableName,@PreFix,@Suffix

				WHILE @@FETCH_STATUS=0
				BEGIN
					IF @PreFix='NIR_log_BBS'
					BEGIN
						SET @SQL=N'
						EXEC [dbo].[DataProxy_NIR_log_BBS_CreateTable] '+@TableName+','''+@Suffix+'''
						INSERT INTO NIRLOG'+LEFT(@Suffix,6)+'.dbo.'+@TableName+'
									(
										--Recno            , 
										UnitID           , 
										ClientNo         , 
										--ClientID         , 
										ComputerIP       , 
										ComputerMAC      , 
										HyperID          , 
										ClientName       , 
										NameSpell        , 
										Certificate_Type , 
										Certificate_Code , 
										CardType         , 
										NCardNo          , 
										OccurTime        , 
										Service_Type     , 
										Title            , 
										SubTitle         , 
										Content          , 
										TagName          , 
										Account          , 
										Password         , 
										URL              , 
										BBSFlag          , 
										ProtocolType       

									)	
						SELECT [UnitID]
							  ,[ClientNo]
							  ,[ComputerIP]
							  ,[ComputerMAC]
							  ,[HyperID]
							  ,[ClientName]
							  ,[NameSpell]
							  ,[Certificate_Type]
							  ,[Certificate_Code]
							  ,[CardType]
							  ,[NCardNo]
							  ,[OccurTime]
							  ,[Service_Type]
							  ,[Title]
							  ,[SubTitle]
							  ,[Content]
							  ,[TagName]
							  ,[Account]
							  ,[Password]
							  ,[URL]
							  ,[BBSFlag]
							  ,[ProtocolType]
							   FROM NIR.dbo.'+@TableName+' WITH(NOLOCK) 
							   ORDER BY RecNo
						'
					END
					ELSE 	IF @PreFix='NIR_log_ChatFetion' 
							OR @PreFix='NIR_log_ChatMSN' 
							OR @PreFix='NIR_log_ChatQQ'
							OR @PreFix='NIR_log_ChatUC' 
							OR @PreFix='NIR_log_ChatYahoo'
					BEGIN
						SET @SQL=N'
						EXEC [dbo].[DataProxy_'+@PreFix+'_CreateTable] '+@TableName+','''+@Suffix+'''
						INSERT INTO NIRLOG'+LEFT(@Suffix,6)+'.dbo.'+@TableName+'
									(
										SessionID	,
										CyberID	 ,
										FriendCyberID	,
										OccurTime	,
										Way	        ,
										Content
									)	
						SELECT  SessionID	,
								CyberID	 ,       
								FriendCyberID	,
								OccurTime	,
								Way	        ,
								Content          
											 FROM NIR.dbo.'+@TableName+' WITH(NOLOCK) 
											 ORDER BY RecNo
						'
					END  
					ELSE 	IF @PreFix='NIR_log_ChatFetionGroup' 
							OR @PreFix='NIR_log_ChatMSNGroup'
							OR @PreFix='NIR_log_ChatQQGroup' 
							OR @PreFix='NIR_log_ChatUCGroup' 
							OR @PreFix='NIR_log_ChatYahooGroup'

					BEGIN
						SET @SQL=N'
						EXEC [dbo].[DataProxy_'+@PreFix+'_CreateTable] '+@TableName+','''+@Suffix+'''
						INSERT INTO NIRLOG'+LEFT(@Suffix,6)+'.dbo.'+@TableName+'
									(
										SessionID   , 
										CyberID     , 
										ChatCyberID , 
										GroupID     , 
										OccurTime   , 
										Content           
									)	
						SELECT  SessionID   , 
								CyberID     , 
								ChatCyberID , 
								GroupID     , 
								OccurTime   , 
								Content       
											FROM NIR.dbo.'+@TableName+' WITH(NOLOCK) 
											ORDER BY RecNo
						'
					END  

					EXEC(@SQL)
					--统计转存后日志表的记录数，如果和源数据表记录数相等，则删除原数据库表
					SET @SQL=N'USE NIRLOG'+LEFT(@Suffix,6)+'
						DECLARE @ResultCount INT       --转存后表的记录数
						 SELECT @ResultCount=rows 
											FROM sysindexes    
											WHERE indid<2
											AND  OBJECT_NAME(id)='''+@TableName+'''  
						IF @ResultCount= (SELECT RecCount 
														FROM #TEMP 
														WHERE TableName='''+@TableName+''')
						--PRINT ''1''
						DROP TABLE NIR.dbo.'+@TableName+'
						ELSE
							BEGIN 
								PRINT ''表NIR.dbo.'+@TableName+'数据转存出错，请操作的同事手工转存''
								SET @Return =-1
							END
						'
					--PRINT @SQL
					EXEC sp_executesql @SQL,N'@Return int OUTPUT',@Return OUTPUT
				FETCH NEXT FROM TEMP1_CURSOR INTO @TableName,@PreFix,@Suffix
				END
				CLOSE TEMP1_CURSOR
				DEALLOCATE TEMP1_CURSOR
			END
		END   

		--处理表NIR_log_SearchKeyword数据
		--建表记录需要处理的表名称,表记录数等信息
		CREATE TABLE #TEMP2
		(
			TableName  NVARCHAR(50),
			RecCount INT,
			PreFix NVARCHAR(30),
			Date NVARCHAR(10),
			Suffix VARCHAR(1)
		)

		--查询需要处理的表对应的记录数、日期等信息
		INSERT INTO #TEMP2(TableName,RecCount,PreFix,Date,Suffix)
		SELECT  T1.name AS TableName,
				T2.rows AS RecCount,
				SUBSTRING(T1.name,1,CHARINDEX('2',T1.name)-1) AS PreFix,
				LEFT(RIGHT(t1.name,7),6) AS Date,
				RIGHT(t1.name,1) AS Suffix	
					FROM SYS.TABLES T1
					INNER JOIN (SELECT OBJECT_NAME(id) AS name,rows  
													FROM sysindexes 
													WHERE indid<2) T2
					ON T1.name=T2.name
					WHERE T1.name like 'NIR_log_SearchKeyword_______'
					AND RIGHT(t1.name,7)>=LEFT(CONVERT(NVARCHAR(8),@StartTime,112),6)+
												(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 1 AND 10 THEN 'A'
													  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 11 AND 20 THEN 'B'
													  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)>=21  THEN 'C'
													  ELSE NULL
													  END)
					AND RIGHT(t1.name,7)<=LEFT(CONVERT(NVARCHAR(8),@EndTime,112),6)+
												(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 1 AND 10 THEN 'A'
													  WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 11 AND 20 THEN 'B'
													  WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)>=21  THEN 'C'
													  ELSE NULL
													  END)
					ORDER by T1.name

		
		IF EXISTS(SELECT 1 FROM #TEMP2)
		BEGIN
			SET @Return=2
			IF @FUNCTIONTYPE=2
			BEGIN				
				DECLARE @i INT,
						@Date2 NVARCHAR(8),
						@Sum INT,
						@Start INT,
						@End INT
				DECLARE TEMP2_CURSOR CURSOR 
						FOR SELECT  TableName,
									RecCount,
									PreFix,
									Date,
									Suffix
											FROM #TEMP2
				DECLARE	@RecCount INT,
						@Date NVARCHAR(10)
				OPEN TEMP2_CURSOR
				FETCH NEXT FROM TEMP2_CURSOR 
						   INTO @TableName,@RecCount,@PreFix,@Date,@Suffix
				WHILE @@FETCH_STATUS=0
				BEGIN
					IF RIGHT(@TableName,7)<>LEFT(CONVERT(NVARCHAR(8),@StartTime,112),6)+
													(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 1 AND 10 THEN 'A'
														  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 11 AND 20 THEN 'B'
														  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)>=21  THEN 'C'
														  ELSE NULL
														  END)
					AND RIGHT(@TableName,7)<>LEFT(CONVERT(NVARCHAR(8),@EndTime,112),6)+
													(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 1 AND 10 THEN 'A'
														  WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 11 AND 20 THEN 'B'
														  WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)>=21  THEN 'C'
														  ELSE NULL
														  END)
					BEGIN
						IF @Suffix='A'
						BEGIN
							SET @i=1
							SET @Sum=0
							WHILE @i<11
							BEGIN   
								SET @Date2=@Date+RIGHT('00'+CAST(@i AS NVARCHAR(2)),2)
								SET @SQL=N'
									EXEC NIR.dbo.DataProxy_NIR_log_SearchKeyword_CreateTable ''1'','''+@Date2+'''
									INSERT INTO NIRLOG'+@Date+'.dbo.NIR_log_SearchKeyword'+@Date2+'
												(
													UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword		 
												)
									SELECT 			UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword	
													FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'A WITH(NOLOCK) 
													WHERE OccurTime='''+@Date2+'''
													AND  OccurTime<DATEADD(DD,1,'''+@Date2+''')
									'
								EXEC(@SQL)
								SET @SQL=N'USE NIRLOG'+@Date+'
									DECLARE @Count INT,@OldCount INT
									SET @Count=0
									SELECT @OldCount=RecCount
												FROM #TEMP5 
												WHERE TableName=''NIR_log_SearchKeyword'+@Date2+'''
									IF ISNULL(@OldCount,'''')=''''
									SET @OldCount=0
									SELECT @Count=rows FROM sysindexes
															WHERE indid<2
															AND OBJECT_NAME(id)=''NIR_log_SearchKeyword'+@Date2+'''
									SET @Sum=@Sum+@Count-@OldCount
									'
									EXEC sp_executesql @SQL,N'@Sum INT OUTPUT',@Sum OUTPUT
								SET @i=@i+1				
							END
							SET @SQL='IF '+CONVERT(NVARCHAR(8),@Sum)+'=(SELECT RecCount
																					FROM #TEMP2
																					WHERE TableName=''NIR_log_SearchKeyword'+@Date+'A'')
										DROP TABLE NIR.dbo.NIR_log_SearchKeyword'+@Date+'A
										ELSE 
										BEGIN 
											PRINT ''表NIR.dbo.NIR_log_SearchKeyword'+@Date+'A数据转存出错，请操作的同事手工转存''
											SET @Return =-1
										END
										'
							EXEC sp_executesql @SQL,N'@Return INT OUTPUT',@Return OUTPUT
						END
						ELSE IF @Suffix='B'
						BEGIN
							SET @i=11 
							SET @Sum=0  
							WHILE @i<21
							BEGIN   
								SET @Date2=@Date+RIGHT('00'+CAST(@i AS NVARCHAR(2)),2)
								SET @SQL=N'
									EXEC NIR.dbo.DataProxy_NIR_log_SearchKeyword_CreateTable ''1'','''+@Date2+'''
									INSERT INTO NIRLOG'+@Date+'.dbo.NIR_log_SearchKeyword'+@Date2+'
												(
													UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword		 
												)
									SELECT 			UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword	
													FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'B WITH(NOLOCK) 
													WHERE OccurTime='''+@Date2+'''
													AND  OccurTime<DATEADD(DD,1,'''+@Date2+''')
									'
									EXEC(@SQL)
								SET @SQL=N'USE NIRLOG'+@Date+'
									DECLARE @Count INT,@OldCount INT
									SET @Count=0
									SELECT @OldCount=RecCount
												FROM #TEMP5 
												WHERE TableName=''NIR_log_SearchKeyword'+@Date2+'''
									IF ISNULL(@OldCount,'''')=''''
									SET @OldCount=0
									SELECT @Count=rows FROM sysindexes
															WHERE indid<2
															AND OBJECT_NAME(id)=''NIR_log_SearchKeyword'+@Date2+'''
									SET @Sum=@Sum+@Count-@OldCount
									'
									EXEC sp_executesql @SQL,N'@Sum INT OUTPUT',@Sum OUTPUT
								SET @i=@i+1				
							END
							SET @SQL='IF '+CONVERT(NVARCHAR(8),@Sum)+'=(SELECT RecCount
														FROM #TEMP2
														WHERE TableName=''NIR_log_SearchKeyword'+@Date+'B'')
										DROP TABLE NIR.dbo.NIR_log_SearchKeyword'+@Date+'B
										ELSE 
										BEGIN 
											PRINT ''表NIR.dbo.NIR_log_SearchKeyword'+@Date+'B数据转存出错，请操作的同事手工转存''
											SET @Return =-1
										END
										'
							EXEC sp_executesql @SQL,N'@Return INT OUTPUT',@Return OUTPUT
						END 
						ELSE IF @Suffix='C'
						BEGIN
							SET @i=21 
							SET @Sum=0  
							WHILE @i<DAY(DATEADD(DD,-1,DATEADD(mm,1,@Date+'01')))+1
							BEGIN   
								SET @Date2=@Date+RIGHT('00'+CAST(@i AS NVARCHAR(2)),2)
								SET @SQL=N'
									EXEC NIR.dbo.DataProxy_NIR_log_SearchKeyword_CreateTable ''1'','''+@Date2+'''
									INSERT INTO NIRLOG'+@Date+'.dbo.NIR_log_SearchKeyword'+@Date2+'
												(
													UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword		 
												)
									SELECT 			UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword	
													FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'C WITH(NOLOCK) 
													WHERE OccurTime='''+@Date2+'''
													AND  OccurTime<DATEADD(DD,1,'''+@Date2+''')
									'
									EXEC(@SQL)
								SET @SQL=N'USE NIRLOG'+@Date+'
									DECLARE @Count INT,@OldCount INT
									SET @Count=0
									SELECT @OldCount=RecCount
												FROM #TEMP5 
												WHERE TableName=''NIR_log_SearchKeyword'+@Date2+'''
									IF ISNULL(@OldCount,'''')=''''
									SET @OldCount=0
									SELECT @Count=rows FROM sysindexes
															WHERE indid<2
															AND OBJECT_NAME(id)=''NIR_log_SearchKeyword'+@Date2+'''
									SET @Sum=@Sum+@Count-@OldCount
									'
									EXEC sp_executesql @SQL,N'@Sum INT OUTPUT',@Sum OUTPUT
								SET @i=@i+1				
							END
							SET @SQL='IF '+CONVERT(NVARCHAR(8),@Sum)+'=(SELECT RecCount
														FROM #TEMP2
														WHERE TableName=''NIR_log_SearchKeyword'+@Date+'C'')
										DROP TABLE NIR.dbo.NIR_log_SearchKeyword'+@Date+'C
										ELSE 
										BEGIN 
											PRINT ''表NIR.dbo.NIR_log_SearchKeyword'+@Date+'C数据转存出错，请操作的同事手工转存''
											SET @Return =-1
										END
										'
							EXEC sp_executesql @SQL,N'@Return INT OUTPUT',@Return OUTPUT
						END  
					END
					ELSE IF RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@StartTime,112),6)+
													(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 1 AND 10 THEN 'A'
														  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 11 AND 20 THEN 'B'
														  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)>=21  THEN 'C'
														  ELSE NULL
														  END)
					OR RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@EndTime,112),6)+
													(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 1 AND 10 THEN 'A'
														  WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 11 AND 20 THEN 'B'
														  WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)>=21  THEN 'C'
														  ELSE NULL
														  END)
					BEGIN
						IF @Suffix='A'
						BEGIN                  
							 IF RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@StartTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 1 AND 10 THEN 'A'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 11 AND 20 THEN 'B'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)>=21  THEN 'C'
															  ELSE NULL
															  END)
							AND RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@EndTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 1 AND 10 THEN 'A'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 11 AND 20 THEN 'B'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)>=21  THEN 'C'
																ELSE NULL
																END)
							 BEGIN
								 SET @Start=RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)
								 SET @End=RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)+1
							 END 
							 ELSE IF RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@StartTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 1 AND 10 THEN 'A'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 11 AND 20 THEN 'B'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)>=21  THEN 'C'
															  ELSE NULL
															  END)
							 BEGIN
								 SET @Start=RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)
								 SET @End=11
							 END 
							 ELSE IF  RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@EndTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 1 AND 10 THEN 'A'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 11 AND 20 THEN 'B'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)>=21  THEN 'C'
																ELSE NULL
																END)
							 BEGIN
								 SET @Start=1
								 SET @End=RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)+1					 
							 END         
							SET @i=@Start
							SET @Sum=0
							WHILE @i<@End
							BEGIN   
								SET @Date2=@Date+RIGHT('00'+CAST(@i AS NVARCHAR(2)),2)
								SET @SQL=N'
									EXEC NIR.dbo.DataProxy_NIR_log_SearchKeyword_CreateTable ''1'','''+@Date2+'''
									INSERT INTO NIRLOG'+@Date+'.dbo.NIR_log_SearchKeyword'+@Date2+'
												(
													UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword		 
												)
									SELECT 			UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword	
													FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'A WITH(NOLOCK) 
													WHERE OccurTime='''+@Date2+'''
													AND  OccurTime<DATEADD(DD,1,'''+@Date2+''')
									'
								EXEC(@SQL)
								SET @SQL=N'USE NIRLOG'+@Date+'
									DECLARE @Count INT,@OldCount INT
									SET @Count=0
									SELECT @OldCount=RecCount 
												FROM #TEMP5
												WHERE TableName=''NIR_log_SearchKeyword'+@Date2+'''
									IF ISNULL(@OldCount,'''')=''''
									SET @OldCount=0
									SELECT @Count=rows FROM sysindexes
															WHERE indid<2
															AND OBJECT_NAME(id)=''NIR_log_SearchKeyword'+@Date2+'''
									IF (@Count-@OldCount)=(SELECT COUNT(1)
																	FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'A WITH(NOLOCK) 
																	WHERE OccurTime='''+@Date2+'''
																	AND  OccurTime<DATEADD(DD,1,'''+@Date2+'''))
									DELETE FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'A
											WHERE OccurTime='''+@Date2+'''
											AND  OccurTime<DATEADD(DD,1,'''+@Date2+''')
									ELSE 
									BEGIN
											PRINT ''表NIR.dbo.NIR_log_SearchKeyword'+@Date+'A数据转存出错，请操作的同事手工转存''
											SET @Return =-1
									END

									'
									EXEC sp_executesql @SQL,N'@Return INT OUTPUT',@Return OUTPUT
								SET @i=@i+1				
							END
						END
						ELSE IF @Suffix='B'
						BEGIN
							 IF RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@StartTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 1 AND 10 THEN 'A'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 11 AND 20 THEN 'B'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)>=21  THEN 'C'
															  ELSE NULL
															  END)
							AND RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@EndTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 1 AND 10 THEN 'A'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 11 AND 20 THEN 'B'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)>=21  THEN 'C'
																ELSE NULL
																END)
							 BEGIN
								 SET @Start=RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)
								 SET @End=RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)+1
							 END 
							 ELSE IF  RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@StartTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 1 AND 10 THEN 'A'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 11 AND 20 THEN 'B'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)>=21  THEN 'C'
															  ELSE NULL
															  END)
							 BEGIN
								 SET @Start=RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)
								 SET @End=21
							 END 
							 ELSE IF  RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@EndTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 1 AND 10 THEN 'A'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 11 AND 20 THEN 'B'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)>=21  THEN 'C'
																ELSE NULL
																END)
							 BEGIN
								 SET @Start=11
								 SET @End=RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)+1					 
							 END         
							SET @i=@Start
							SET @Sum=0
							WHILE @i<@End
							BEGIN   
								SET @Date2=@Date+RIGHT('00'+CAST(@i AS NVARCHAR(2)),2)
								SET @SQL=N'
									EXEC NIR.dbo.DataProxy_NIR_log_SearchKeyword_CreateTable ''1'','''+@Date2+'''
									INSERT INTO NIRLOG'+@Date+'.dbo.NIR_log_SearchKeyword'+@Date2+'
												(
													UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword		 
												)
									SELECT 			UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword	
													FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'B WITH(NOLOCK) 
													WHERE OccurTime='''+@Date2+'''
													AND  OccurTime<DATEADD(DD,1,'''+@Date2+''')
									'
									EXEC(@SQL)
								SET @SQL=N'USE NIRLOG'+@Date+'
									DECLARE @Count INT,@OldCount INT
									SET @Count=0
									SELECT @OldCount=RecCount 
												FROM #TEMP5
												WHERE TableName=''NIR_log_SearchKeyword'+@Date2+'''
									IF ISNULL(@OldCount,'''')=''''
									SET @OldCount=0
									SELECT @Count=rows FROM sysindexes
															WHERE indid<2
															AND OBJECT_NAME(id)=''NIR_log_SearchKeyword'+@Date2+'''
									IF (@Count-@OldCount)=(SELECT COUNT(1)
																	FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'B WITH(NOLOCK) 
																	WHERE OccurTime='''+@Date2+'''
																	AND  OccurTime<DATEADD(DD,1,'''+@Date2+'''))
									DELETE FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'B
											WHERE OccurTime='''+@Date2+'''
											AND  OccurTime<DATEADD(DD,1,'''+@Date2+''')
									ELSE 
									BEGIN
											PRINT ''表NIR.dbo.NIR_log_SearchKeyword'+@Date+'B数据转存出错，请操作的同事手工转存''
											SET @Return =-1
									END

									'
									EXEC sp_executesql @SQL,N'@Return INT OUTPUT',@Return OUTPUT
								SET @i=@i+1				
							END
						END 
						ELSE IF @Suffix='C'
						BEGIN
							 IF  RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@StartTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 1 AND 10 THEN 'A'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 11 AND 20 THEN 'B'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)>=21  THEN 'C'
															  ELSE NULL
															  END)
							AND RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@EndTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 1 AND 10 THEN 'A'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 11 AND 20 THEN 'B'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)>=21  THEN 'C'
																ELSE NULL
																END)
							 BEGIN
								 SET @Start=RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)
								 SET @End=RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)+1
							 END 
							 ELSE IF RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@StartTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 1 AND 10 THEN 'A'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2) BETWEEN 11 AND 20 THEN 'B'
															  WHEN RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)>=21  THEN 'C'
															  ELSE NULL
															  END)
							 BEGIN
								 SET @Start=RIGHT(CONVERT(NVARCHAR(8),@StartTime,112),2)
								 SET @End=DAY(DATEADD(DD,-1,DATEADD(mm,1,@Date+'01')))+1
							 END 
							 ELSE IF RIGHT(@TableName,7)=LEFT(CONVERT(NVARCHAR(8),@EndTime,112),6)+
														(CASE WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 1 AND 10 THEN 'A'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2) BETWEEN 11 AND 20 THEN 'B'
																WHEN RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)>=21  THEN 'C'
																ELSE NULL
																END)
							 BEGIN
								 SET @Start=21
								 SET @End=RIGHT(CONVERT(NVARCHAR(8),@EndTime,112),2)+1					 
							 END        
							SET @i=@Start
							SET @Sum=0
							WHILE @i<@End                  
							BEGIN   
								SET @Date2=@Date+RIGHT('00'+CAST(@i AS NVARCHAR(2)),2)
								SET @SQL=N'
									EXEC NIR.dbo.DataProxy_NIR_log_SearchKeyword_CreateTable ''1'','''+@Date2+'''
									INSERT INTO NIRLOG'+@Date+'.dbo.NIR_log_SearchKeyword'+@Date2+'
												(
													UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword		 
												)
									SELECT 			UnitID		,
													ClientNo	,
													--ClientID	,
													ComputerIP	,
													ComputerMAC	,
													HyperID		,
													ClientName	,
													NameSpell	,
													Certificate_Type,
													Certificate_Code,
													CardType	,
													NCardNo		,
													OccurTime	,
													SearchType	,
													Keyword	
													FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'C WITH(NOLOCK) 
													WHERE OccurTime='''+@Date2+'''
													AND  OccurTime<DATEADD(DD,1,'''+@Date2+''')
									'
									EXEC(@SQL)
								SET @SQL=N'USE NIRLOG'+@Date+'
									DECLARE @Count INT,@OldCount INT
									SET @Count=0
									SELECT @OldCount=RecCount 
												FROM #TEMP5
												WHERE TableName=''NIR_log_SearchKeyword'+@Date2+'''
									IF ISNULL(@OldCount,'''')=''''
									SET @OldCount=0
									SELECT @Count=rows FROM sysindexes
															WHERE indid<2
															AND OBJECT_NAME(id)=''NIR_log_SearchKeyword'+@Date2+'''
									IF (@Count-@OldCount)=(SELECT COUNT(1)
																	FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'C WITH(NOLOCK) 
																	WHERE OccurTime='''+@Date2+'''
																	AND  OccurTime<DATEADD(DD,1,'''+@Date2+'''))
									DELETE FROM NIR.dbo.NIR_log_SearchKeyword'+@Date+'C
											WHERE OccurTime='''+@Date2+'''
											AND  OccurTime<DATEADD(DD,1,'''+@Date2+''')
									ELSE 
									BEGIN
											PRINT ''表NIR.dbo.NIR_log_SearchKeyword'+@Date+'C数据转存出错，请操作的同事手工转存''
											SET @Return =-1
									END

									'
									EXEC sp_executesql @SQL,N'@Return INT OUTPUT',@Return OUTPUT
								SET @i=@i+1				
							END
						END 
					END
					FETCH NEXT FROM TEMP2_CURSOR 
							INTO @TableName,@RecCount,@PreFix,@Date,@Suffix
				END              
				CLOSE TEMP2_CURSOR
				DEALLOCATE TEMP2_CURSOR
			END
		END

		--处理表NIR_log_TelSession\NIR_log_CyberLogin\NIR_re_ClientCyber的数据
		--建表记录需要处理的记录的日期及记录数
		CREATE TABLE #TEMP3
		(
			TableName NVARCHAR(100),
			Date NVARCHAR(8),
			Count INT
		)

		INSERT INTO #TEMP3
		SELECT  'NIR_log_CyberLogin' AS TableName,
				CONVERT(NVARCHAR(8),LoginTime,112) AS Date,
				count(distinct cast(SessionID as nvarchar(20))+convert(nvarchar(20),LoginTime,120)) AS Count
						FROM NIR_log_CyberLogin WITH(NOLOCK) 
						WHERE LoginTime>=@StartTime
						AND LoginTime<DATEADD(DD,1,@EndTime)
						GROUP BY CONVERT(NVARCHAR(8),LoginTime,112)
		UNION ALL
		SELECT  'NIR_log_TelSession' AS TableName,
				CONVERT(NVARCHAR(8),OccurTime,112) AS Date,
				COUNT(1) AS Count
						FROM NIR_log_TelSession WITH(NOLOCK) 
						WHERE OccurTime>=@StartTime
						AND OccurTime<DATEADD(DD,1,@EndTime)
						GROUP BY CONVERT(NVARCHAR(8),OccurTime,112)
		UNION ALL 
		SELECT  'NIR_re_ClientCyber' AS TableName,
				CONVERT(NVARCHAR(8),RecordTime,112) AS Date,
				COUNT(1) AS Count
						FROM NIR_re_ClientCyber WITH(NOLOCK) 
						WHERE RecordTime>=@StartTime
						AND RecordTime<DATEADD(DD,1,@EndTime)
						GROUP BY CONVERT(NVARCHAR(8),RecordTime,112)
				ORDER BY TableName,Date

		IF EXISTS(SELECT 1
								FROM #TEMP3)
		BEGIN
			SET @Return=2
			IF @FUNCTIONTYPE=2
			BEGIN      
				DECLARE TEMP3_CURSOR CURSOR FOR 
						SELECT TableName,Date FROM #TEMP3	
				OPEN TEMP3_CURSOR
				FETCH NEXT FROM TEMP3_CURSOR INTO  @TableName,@Date
				WHILE @@FETCH_STATUS=0
				BEGIN
					IF @TableName='NIR_log_CyberLogin'
					BEGIN
						SET @SQL=N'
							EXEC NIR.dbo.DataProxy_'+@TableName+'_CreateTable ''1'','''+@Date+'''
							INSERT INTO NIRLOG'+LEFT(@Date,6)+'.dbo.'+@TableName+@Date+'
										(
											SessionID	,
											LoginTime	,
											--Recno		,
											CyberID		,
											UnitID		,
											LogoutTime	 
										)
							SELECT  SessionID,
									LoginTime,
									MAX(CyberID) AS CyberID,
									MAX(UnitID) AS UnitID,
									MAX(LogoutTime) AS LogoutTime
									FROM NIR.dbo.'+@TableName+' WITH(NOLOCK) 
									WHERE LoginTime>='''+@Date+'''
									AND LoginTime<DATEADD(DD,1,'''+@Date+''')
									GROUP BY SessionID,LoginTime
							'
						EXEC (@SQL)
						SET @SQL=N'USE NIRLOG'+LEFT(@Date,6)+'
							DECLARE @Count INT,@OldCount INT
							SET @Count=0
							SET @OldCount=0
							IF EXISTS(SELECT 1 
												FROM #TEMP5
												WHERE TableName='''+@TableName+@Date+''')
							SELECT @OldCount=RecCount
										FROM #TEMP5
										WHERE TableName='''+@TableName+@Date+'''
							SELECT @Count=rows FROM sysindexes
												WHERE indid<2
												AND OBJECT_NAME(id)='''+@TableName+@Date+'''
							IF (@Count-@OldCount)=(SELECT count(1) FROM 
																	(SELECT DISTINCT SessionID,LoginTime 
																			FROM NIR.dbo.NIR_log_CyberLogin T2
																			where T2.LoginTime>='''+@Date+'''
																			and T2.LoginTime<DATEADD(DD,1,'''+@Date+''')
																	)B)
							DELETE FROM NIR.dbo.'+@TableName+'
										WHERE LoginTime>='''+@Date+'''
										AND LoginTime<DATEADD(DD,1,'''+@Date+''')
							ELSE 
							BEGIN
									PRINT ''表NIR.dbo.'+@TableName+' '+@Date+'这天的数据转存出错，请操作的同事手工转存''
									SET @Return =-1
							END              
							'
						EXEC sp_executesql @SQL,N'@Return INT OUTPUT',@Return OUTPUT
					END
					ELSE IF @TableName='NIR_log_TelSession'
					BEGIN
						SET @SQL=N'
							EXEC NIR.dbo.DataProxy_'+@TableName+'_CreateTable ''1'','''+@Date+'''
							--先记录转存数据前目标表的指定日期内原有数据量
							INSERT INTO #TEMP5
							SELECT ''NIRLOG'+LEFT(@Date,6)+''' AS DatabaseName,
								   '''+@TableName+@Date+''' AS TableName,
								   COUNT(1) AS RecCount
										FROM NIRLOG'+LEFT(@Date,6)+'.dbo.NIR_log_TelSession
										WHERE OccurTime>='''+@Date+'''
										AND OccurTime<DATEADD(DD,1,'''+@Date+''')
							INSERT INTO NIRLOG'+LEFT(@Date,6)+'.dbo.'+@TableName+'
										(
											--RecNo		,
											TelId		,
											SessionID	,
											FriendCyberID	,
											OccurTime	 	 
										)
							SELECT  --RecNo		,
									TelId		,
									SessionID	,
									FriendCyberID	,
									OccurTime	 
									FROM NIR.dbo.'+@TableName+' WITH(NOLOCK) 
									WHERE OccurTime>='''+@Date+'''
									AND OccurTime<DATEADD(DD,1,'''+@Date+''')
							'
						EXEC (@SQL)
						SET @SQL=N'USE NIRLOG'+LEFT(@Date,6)+'
							DECLARE @Count INT,@OldCount INT
							SET @Count=0
							SET @OldCount=0
							IF EXISTS(SELECT 1 
												FROM #TEMP5
												WHERE TableName='''+@TableName+@Date+''')
							SELECT @OldCount=RecCount
										FROM #TEMP5
										WHERE TableName='''+@TableName+@Date+'''
							SELECT @Count=COUNT(1) FROM NIRLOG'+LEFT(@Date,6)+'.dbo.'+@TableName+' WITH(NOLOCK) 
												   WHERE OccurTime>='''+@Date+'''
												   AND OccurTime<DATEADD(DD,1,'''+@Date+''')
							IF (@Count-@OldCount)=(SELECT Count 
															FROM #TEMP3
															WHERE Date='''+@Date+'''
															AND TableName='''+@TableName+''')
							DELETE FROM NIR.dbo.'+@TableName+'
										WHERE OccurTime>='''+@Date+'''
										AND OccurTime<DATEADD(DD,1,'''+@Date+''')
							ELSE 
							BEGIN
									PRINT ''表NIR.dbo.'+@TableName+' '+@Date+'这天的数据转存出错，请操作的同事手工转存''
									SET @Return =-1
							END              
							'
						EXEC sp_executesql @SQL,N'@Return INT OUTPUT',@Return OUTPUT
					END
					ELSE IF @TableName='NIR_re_ClientCyber'
					BEGIN
						SET @SQL=N'
							EXEC NIR.dbo.DataProxy_'+@TableName+'_CreateTable ''1'','''+@Date+'''
							INSERT INTO NIRLOG'+LEFT(@Date,6)+'.dbo.'+@TableName+@Date+'
										(
											UnitID		,
											ClientNo	,
											CyberID		,
											CyberType	,
											RecordTime	,
											--ClientID	,
											SessionID	 
 
										)
							SELECT  UnitID		,
									ClientNo	,
									CyberID		,
									CyberType	,
									RecordTime	,
									--ClientID	,
									SessionID	 
									FROM NIR.dbo.'+@TableName+' WITH(NOLOCK) 
									WHERE RecordTime>='''+@Date+'''
									AND RecordTime<DATEADD(DD,1,'''+@Date+''')
							'
						EXEC (@SQL)
						SET @SQL=N'USE NIRLOG'+LEFT(@Date,6)+'
							DECLARE @Count INT,@OldCount INT
							SET @Count=0
							SET @OldCount=0
							IF EXISTS(SELECT 1 
												FROM #TEMP5
												WHERE TableName='''+@TableName+@Date+''')
							SELECT @OldCount=RecCount
										FROM #TEMP5
										WHERE TableName='''+@TableName+@Date+'''
							SELECT @Count=rows FROM sysindexes
												WHERE indid<2
												AND OBJECT_NAME(id)='''+@TableName+@Date+'''
							IF (@Count-@OldCount)=(SELECT Count 
																FROM #TEMP3
																WHERE Date='''+@Date+'''
																AND TableName='''+@TableName+''')
							DELETE FROM NIR.dbo.'+@TableName+'
										WHERE RecordTime>='''+@Date+'''
										AND RecordTime<DATEADD(DD,1,'''+@Date+''')
							ELSE 
							BEGIN
									PRINT ''表NIR.dbo.'+@TableName+' '+@Date+'这天的数据转存出错，请操作的同事手工转存''
									SET @Return =-1
							END              
							'
						EXEC sp_executesql @SQL,N'@Return INT OUTPUT',@Return OUTPUT
					END   
					FETCH NEXT FROM TEMP3_CURSOR INTO  @TableName,@Date       		
				END 
				CLOSE TEMP3_CURSOR
				DEALLOCATE TEMP3_CURSOR   
			END
		END  
	END




                

        

    

    
  



    

      

        

            

              
                
                  smahorse
                
              
            

            

                关注
              
            

        

        

          
      

      










            

  



          




        



    





    



      

      

        
      

      





	

		评论	
	

  
	




  

    

      添加红包
      
    

    

      

        
        

          
          
        

        
请填写红包祝福语或标题

      

      

        
        

          
          个
        

        
红包个数最小为10个

      

      

        
        

          
          元
        

        
红包金额最低5元

      

      

        
        

          当前余额3.43元
          前往充值 >
        

      

      

        

          需支付：10.00元
        

        
        
      

    

  





  

    
    
  

  

    
    
  








  

    

      

        

          

            
            
成就一亿技术人!

          

          

        

        

          

          

            领取后你会自动成为博主和红包主的粉丝
            规则
          

        

      

      

        

          

            
              
            
            

              
hope_wisdom
 发出的红包
            

          

        

        

          

          

          

        

      

    

    

  



      
      

      
实付元

      
使用余额支付

      

        

          

            
            点击重新获取
          

        

        
扫码支付

      

      

        
          
            
          
          
        
      

      

        
        钱包余额
          0
          

            
            

              

                
抵扣说明：

                
 1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
 2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

              

            

          

      

      余额充值