TSzqy3r4

1DECLARE @retainOnAndAfterDate varchar(20)
2
3-- Optional:  update/change the date
4SET @retainOnAndAfterDate = '1/1/2018'
5
6IF NOT EXISTS
7(
8SELECT
9*
10FROM
11SysObjects O
12WHERE
13ObjectProperty(O.ID,'IsUserTable')=1
14AND
15O.Name='tmpStatusMessageSaveRows'
16)
17BEGIN
18CREATE TABLE tmpStatusMessageSaveRows (
19	[StatusMessageId] [uniqueidentifier] NOT NULL,
20	[Partition] [nvarchar](50) NOT NULL,
21	[ThreadName] [nvarchar](250) NOT NULL,
22	[MachineName] [nvarchar](250) NOT NULL,
23	[Text] [nvarchar](4000) NOT NULL,
24	[CreatedOn] [datetime] NOT NULL,
25	[Sequence] [bigint] NOT NULL,
26);
27END
28ELSE
29BEGIN
30TRUNCATE TABLE tmpStatusMessageSaveRows
31END
32
33INSERT INTO tmpStatusMessageSaveRows (StatusMessageId,
34	Partition,
35	ThreadName,
36	MachineName,
37	Text,
38	CreatedOn,
39	Sequence
40)
41SELECT StatusMessageId,
42	Partition,
43	ThreadName,
44	MachineName,
45	Text,
46	CreatedOn,
47	Sequence
48FROM tbStatusMessage WHERE CreatedOn > @retainOnAndAfterDate;
49
50TRUNCATE TABLE tbStatusMessage
51
52SET IDENTITY_INSERT tbStatusMessage ON
53INSERT INTO tbStatusMessage (StatusMessageId,
54	Partition,
55	ThreadName,
56	MachineName,
57	Text,
58	CreatedOn,
59	Sequence
60)
61SELECT StatusMessageId,
62	Partition,
63	ThreadName,
64	MachineName,
65	Text,
66	CreatedOn,
67	Sequence
68FROM tmpStatusMessageSaveRows;
69SET IDENTITY_INSERT tbStatusMessage OFF;
70
71DROP TABLE tmpStatusMessageSaveRows