归档脚本

USE [dbtest_history]
GO
/****** Object: StoredProcedure [dbo].[csp_backup_ListingOverdueInterest] Script Date: 12/29/2015 13:29:06 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[csp_backup_ListingOverdueInterest]
AS
BEGIN
DECLARE @tablename VARCHAR(50)
DECLARE @sdate DATETIME
DECLARE @edate DATETIME

IF OBJECT_ID('dbtest_history..filelog', 'U') IS NULL
BEGIN
EXEC ('
use dbtest_history;
CREATE TABLE filelog
(
id INT NOT NULL IDENTITY(1,1) PRIMARY KEY ,
lastFileTime DATETIME NOT NULL ,
tableName nvarchar(50) NOT NULL ,
insertTime DATETIME NOT NULL DEFAULT GETDATE(),
updateTime DATETIME NOT NULL DEFAULT GETDATE(),
isActive BIT NOT NULL DEFAULT 1
) on [PRIMARY]
/*# Owner: nieyan ;Manager: zhaodong #*/
'
)

END

IF ( SELECT MAX(lastFileTime)
FROM dbtest_history.dbo.filelog
) IS NULL
BEGIN
INSERT INTO dbtest_history.dbo.filelog
( lastFileTime ,
tableName
)
SELECT MIN(insertTime) ,
'ListingOverdueInterest'
FROM dbtest.dbo.ListingOverdueInterest with(nolock)
END

SELECT @sdate = cast(MAX(lastFileTime) as DATE) ,
@edate = CAST (GETDATE() AS DATE)
FROM dbtest_history.dbo.filelog WITH ( NOLOCK )

WHILE ( @sdate < @edate )
BEGIN
IF OBJECT_ID('tempdb..#tmp_1', 'U') IS NOT NULL
BEGIN
DROP TABLE #tmp_1
END
SELECT IDENTITY( INT,1,1 ) AS 'LogID' ,
[Id]
INTO #tmp_1
FROM dbtest.dbo.ListingOverdueInterest WITH ( NOLOCK )
WHERE UpdateTime >= @sdate
AND UpdateTime < CAST(DATEADD(DAY, 1, @sdate) AS DATE)

IF EXISTS ( SELECT 1
FROM dbtest.dbo.ListingOverdueInterest
WITH ( NOLOCK )
WHERE CAST(UpdateTime AS DATE) = CAST(@sdate AS DATE) )
BEGIN
SET @tablename = 'ListingOverdueInterestBY'
+ CONVERT(VARCHAR(6), @sdate, 112)
+ CASE WHEN DATEPART(DAY, @sdate) <= 10 THEN 'A'
WHEN DATEPART(DAY, @sdate) > 10
AND DATEPART(DAY, @sdate) <= 20
THEN 'B'
WHEN DATEPART(DAY, @sdate) > 20 THEN 'C'
END
END

IF NOT EXISTS ( SELECT name
FROM dbtest_history.sys.tables
WHERE name = @tablename ) AND @tablename IS NOT NULL
BEGIN
EXEC('USE [dbtest_history];
CREATE TABLE '+@tablename+' (
[Id] [varchar](32) NOT NULL,
[ListingId] [int] NOT NULL,
[LoanId] [int] NOT NULL,
[UserAmount] [money] NOT NULL,
[CorpAmount] [money] NOT NULL,
[RecordStatus] [int] NOT NULL,
[TotalUserAmount] [money] NOT NULL,
[TotalCorpAmount] [money] NOT NULL,
[BorrowerId] [int] NOT NULL,
[OverdueLastDate] [datetime] NOT NULL,
[InsertTime] [datetime] NOT NULL DEFAULT GETDATE(),
[UpdateTime] [datetime] NOT NULL DEFAULT GETDATE(),
[IsActive] [bit] NOT NULL DEFAULT 1,
CONSTRAINT [PK_'+@tablename+'] PRIMARY KEY CLUSTERED
(
[Id] ASC
))ON [PRIMARY]
/*# Owner: nieyan ;Manager: zhaodong #*/
')

END


DECLARE @bid INT
DECLARE @eid INT
DECLARE @sql NVARCHAR(4000)
SET @bid = 0
SELECT @eid = MAX(logid)
FROM #tmp_1

WHILE @bid <= @eid
BEGIN
WAITFOR DELAY '00:00:01'
SET @sql = 'insert into dbtest_history.dbo.'
+ @tablename
+ ' select a.* FROM dbtest.dbo.ListingOverdueInterest as a with(nolock) '
+ ' inner join #tmp_1 as b with(nolock) on a.Id = b.Id and b.LogID > '
+ CONVERT(VARCHAR, @bid) + ' and b.LogID <= '
+ CONVERT(VARCHAR, @bid + 10000)
+ ' and b.LogID <= ' + CONVERT(VARCHAR, @eid)
+ ' where not exists(select id from dbtest_history.dbo.'
+ @tablename + ' where id = a.Id) '
--SELECT @sql
EXEC(@sql)
SET @bid = @bid + 10000
END

SET @sdate = CAST(DATEADD(DAY, 1, @sdate) AS DATE)
END

INSERT INTO dbtest_history.dbo.filelog
( lastFileTime ,
tableName
)
VALUES ( GETDATE() ,
'ListingOverdueInterest'
)

END