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