tsql_exec/archive_table.sql at main · ahkarm/tsql_exec · GitHub

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
-- J_MOV_USR_SYS_LOG_HIS_EXEC

USE [CMS_KBANK]
GO

-- Move Historical Data
BEGIN TRANSACTION;

-- Insert the data into SYSUSERLOG from SYSUSERLOGHIS based on the conditions
;WITH CTE_Transaction AS (
    SELECT
	[LOGREF],
	[LOGDT],
	[ACTION_USRID],
	[TARGET_USRID],
	[ACTION_TYPE],
	[LOG_BODY],
	[LOGSTS]
    FROM SYSUSERLOG
    WHERE [LOGDT] < CAST(GETDATE() AS DATE)
)
-- Insert the selected records into R_LOGTRAN and also delete from D_LOGTRAN
DELETE CTE_Transaction
OUTPUT
	DELETED.[LOGREF],
	DELETED.[LOGDT],
	DELETED.[ACTION_USRID],
	DELETED.[TARGET_USRID],
	DELETED.[ACTION_TYPE],
	DELETED.[LOG_BODY],
	DELETED.[LOGSTS]
INTO SYSUSERLOGHIS;

COMMIT TRANSACTION;

-- J_MOV_SMS_OUT_HIS_EXEC

USE [CMS_KBANK]
GO

-- Move Historical Data
BEGIN TRANSACTION;

-- Insert the data into R_OTPOUT from D_OTPOUT based on the conditions
;WITH CTE_Transaction AS (
    SELECT
    [TXREFID],
	[REFNUM],
	[TXDT],
	[SMSINFO],
	[SMSSTS],
	[SENTDT],
	[EXPIREDT]
    FROM D_SMSOUT
    WHERE [TXDT] < CAST(GETDATE() AS DATE)
)
-- Insert the selected records into R_LOGTRAN and also delete from D_LOGTRAN
DELETE CTE_Transaction
OUTPUT
	deleted.[TXREFID],
	deleted.[REFNUM],
	deleted.[TXDT],
	deleted.[SMSINFO],
	deleted.[SMSSTS],
	deleted.[SENTDT],
	deleted.[EXPIREDT]
INTO R_SMSOUT;

COMMIT TRANSACTION;

-- J_MOV_EMS_OUT_HIS_EXEC

USE [CMS_KBANK]
GO

-- Move Historical Data
BEGIN TRANSACTION;

-- Insert the data into R_OTPOUT from D_OTPOUT based on the conditions
;WITH CTE_Transaction AS (
    SELECT
    [TXREFID],
	[REFNUM],
	[TXDT],
	[MAILINFO],
	[MAILSTS],
	[SENTDT],
	[EXPIREDT]
    FROM D_EMAILOUT
    WHERE [TXDT] < CAST(GETDATE() AS DATE)
)
-- Insert the selected records into R_LOGTRAN and also delete from D_LOGTRAN
DELETE CTE_Transaction
OUTPUT
	deleted.[TXREFID],
	deleted.[REFNUM],
	deleted.[TXDT],
	deleted.[MAILINFO],
	deleted.[MAILSTS],
	deleted.[SENTDT],
	deleted.[EXPIREDT]
INTO R_EMAILOUT;

COMMIT TRANSACTION;