Vision Binary

In most data warehouse scenario, we extract data from one or multi-source and load into our centralize destination. When it comes to load data, we have two methods, one is full load and the other is incremental Load. Today I m gonna post the basic method for incremental load in T-sql. Very useful and practical.

1st, lets create a Staging Table

CREATE TABLE [dbo].[StagingTable]
(
[ID] INT NOT NULL,
[EXPECTEDCOMPLETION] [nvarchar](255) NOT NULL
)
ON [PRIMARY]

GO

2ND SOURCE TABLE

CREATE TABLE [dbo].[DestTable]
(
[ID] [nvarchar](50) NOT NULL,
[EXPECTEDCOMPLETION] [nvarchar](255) NULL,
CONSTRAINT [PK_PER_PatentCalc] PRIMARY KEY CLUSTERED ( [ID] ASC )
WITH ( PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF,
IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON,
ALLOW_PAGE_LOCKS = ON ) ON [PRIMARY]
)
ON [PRIMARY]

GO

Now you can insert values into your staging table.

then INCREMENTAL LOAD USE :INSERT INTO, a SELECT and a LEFT JOIN.

INSERT INTO dbo.DestTable
(
ID,
EXPECTEDCOMPLETION
)
SELECT s.ID,
s.ExpectedCompletion
FROM dbo.StagingTable s
LEFT JOIN dbo.DestTable d ON s.id = d.id
WHERE d.id IS NULL

now you have load those data to your destination table, you can try update some record in your staging table. then run this, you will automatically updated the destination as well.

Update changed Records
UPDATE d
SET d.ID = s.ID,
d.expectedcompletion = s.EXPECTEDCOMPLETION
FROM dbo.DestTable d
INNER JOIN dbo.StagingTable s ON s.ID = d.ID
WHERE s.EXPECTEDCOMPLETION != d.EXPECTEDCOMPLETION

or You can delete some records in staging table, then run this, you will delete the record in destination table which doesnt exist in staging table.

DELETE FROM dbo.StagingTable
WHERE id = 1
DELETE FROM dbo.DestTable
FROM dbo.DestTable d
LEFT JOIN dbo.StagingTable s ON d.ID = s.ID
WHERE s.id IS NULL