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