quAbXGVE

1INSERT in Stored Procedure called via JDBC
21) Put a snapshot of the data contained in CurrentTableA into #CurrentShotA (temporary table)
3
42) Compare #CurrentShotA with PreviousTableA 
5
63) Insert differences into #TempTableB 
7(this equates to new rows or altered rows in #CurrentShotA)
8
94) Empty PreviousTableA
10
115) Insert contents of #CurrentShotA into PreviousTableA
12
136) Select * from #TempTableB (return all new rows and changes)
14	
15CREATE PROCEDURE [dbo].[getUpdatedSchedules] 
16AS
17BEGIN
18-- SET NOCOUNT ON added to prevent extra result sets from
19-- interfering with SELECT statements.
20SET NOCOUNT ON;
21
22-- Check of the temporary table exists, delete if it does
23        IF OBJECT_ID('#TempTableB','U')IS NOT NULL
24        begin
25            drop table #TempTableB
26        end
27
28        -- Force the creation of the temporary tables quickly
29        select * into #TempTableB from dbo.CurrentTableA where 1=0
30        select * into #CurrentShotA from dbo.CurrentTableA where 1=0
31
32        -- Get the differences between schedules and put into #TempTableB
33        insert #CurrentShotA select * from dbo.CurrentTableA
34        insert #TempTableB select * from #CurrentShotA 
35        except select * from dbo.PreviousTableA
36
37        TRUNCATE TABLE dbo.PreviousTableA       
38        insert dbo.PreviousTableA select * from #CurrentShotA
39        select * from #TempTableB
40    END
41GO