clear markdown compare help best fiddles feedback dbanow.uk a leap of faith?
clear markdown donate comments/suggestions/bugs a leap of faith? bible365 diddy dollings
By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0. 1228811 fiddles created (16681 in the last week).

CREATE TABLE dbo.bigtable ( UpdateDate datetime, PK varchar(12), col1 varchar(10), col2 int, col3 varchar(10), PRIMARY KEY CLUSTERED(UpdateDate, PK) ); CREATE TABLE dbo.bigtable_archive ( UpdateDate datetime, PK varchar(12), col1 varchar(10), col2 int, col3 varchar(10), PRIMARY KEY CLUSTERED(UpdateDate, PK) ); INSERT INTO dbo.bigtable VALUES('20170102', 'ABC', 'C3', 1, 'C1'); INSERT INTO dbo.bigtable_archive VALUES('20170101', 'ABC', 'C1', 1, 'C1');
2 rows affected
 hidden batch(es)


CREATE TRIGGER dbo.IoI_BTA ON dbo.bigtable_archive INSTEAD OF INSERT AS BEGIN IF NOT EXISTs(SELECT 1 FROM dbo.bigtable_archive bta INNER JOIN inserted i ON bta.PK = i.PK AND bta.UpdateDate = i.UpdateDate) BEGIN INSERT INTO dbo.bigtable_archive SELECT * FROM inserted; END END
 hidden batch(es)


CREATE TRIGGER dbo.IoI_BT ON dbo.bigtable AFTER INSERT AS BEGIN IF NOT EXISTS(SELECT 1 FROM dbo.bigtable_archive bta INNER JOIN inserted i ON bta.PK = i.PK AND bta.UpdateDate = i.UpdateDate) BEGIN INSERT INTO dbo.bigtable_archive SELECT * FROM inserted; END END
 hidden batch(es)


SELECT * FROM bigtable; SELECT * FROM bigtable_archive;
UpdateDate PK col1 col2 col3
02/01/2017 00:00:00 ABC C3 1 C1
UpdateDate PK col1 col2 col3
01/01/2017 00:00:00 ABC C1 1 C1
 hidden batch(es)


-- inserts last records into _archive talbe INSERT INTO bigtable_archive SELECT * FROM bigtable WHERE UpdateDate >= '20170102';
2 rows affected
 hidden batch(es)


SELECT * FROM bigtable_archive;
UpdateDate PK col1 col2 col3
01/01/2017 00:00:00 ABC C1 1 C1
02/01/2017 00:00:00 ABC C3 1 C1
 hidden batch(es)


-- Now try to insert current bigtable record into bigtable_archive INSERT INTO dbo.bigtable_archive VALUES('20170102', 'ABC', 'C3', 1, 'C1');
1 rows affected
 hidden batch(es)


SELECT * FROM bigtable_archive;
UpdateDate PK col1 col2 col3
01/01/2017 00:00:00 ABC C1 1 C1
02/01/2017 00:00:00 ABC C3 1 C1
 hidden batch(es)