jz6ik6zg

1-- Where all the results get pulled down and held (The table I want to clean up every 2 hours or so)
2CREATE TABLE [reservations].[DumpTable](
3    [utypeID] [bigint] IDENTITY(1,1) NOT NULL
4    -- Other columns
5    CONSTRAINT [PK_UnitTypesFound] PRIMARY KEY CLUSTERED 
6    (
7        [utypeID] ASC
8    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
9) ON [PRIMARY]
10
11
12-- This is one of many other tables that might reference the dump table
13CREATE TABLE [reservations].[OtherTables](
14    [memberID]  INT NOT NULL,
15    [utypeID]   BIGINT NOT NULL -- Need to Know if the dumptable is referenced here
16    CONSTRAINT [PK_MemberUnitTypes] PRIMARY KEY CLUSTERED
17    (
18        [memberID],
19        [utypeID]
20    ) WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF,     ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
21) ON [PRIMARY]
22
23ALTER TABLE [reservations].[OtherTables]  WITH CHECK ADD  CONSTRAINT [FK_OtherTable_DumpTable] FOREIGN KEY([utypeID])
24    REFERENCES [reservations].[DumpTable] ([utypeID])
25
26ALTER TABLE [reservations].[OtherTables] CHECK CONSTRAINT [FK_OtherTable_DumpTable]
27	
28DELETE FROM DATA_TABLE 
29FROM         DATA_TABLE LEFT OUTER JOIN
30                      OTHERON DATA_TABLE.OTHER_TABLE_ID= OTHER.ID 
31WHERE     (OTHER.ID IS NULL) and DATA_TABLE.SOME_TIMESTAMP < threshold_time
32	
33DATA_TABLE
34    OTHER_TABLE_ID    INTEGER
35    SOME_TIMESTAMP    DATETIME
36
37OTHER_TABLE
38    ID                INTEGER
39	
40DELETE FROM DATA_TABLE dt WHERE dt.SOME_TIMESTAMP < threshold_time
41  AND NOT EXISTS (SELECT 1 FROM OTHER TABLE WHERE ID = dt.OTHER_TABLE_ID;
42	
43DELETE FROM [table] WHERE ISNULL([colForeignKey], 0) = 0