14 What are temporal tables in SQL | How to work with temporal tables in SQL

[@nehaghavnalkar3822]

Good information!!
Thank you

[@abdullahquhtani4247]

Is there a way to rollback any executed SQL statement?!

[@learnssis]

No, until unless you have ran Begin tran query before executing your sql statement. If you have ran Begin tran and then executed your query then you have option to rollback the query by running
rollback tran

[@abdullahquhtani4247]

@@learnssis thank you so much for your reply. Could you plz make a tutorial on that because that is the most important thing ppl need specially when they test something against important database.
Thank you again

[@learnssis]

@@abdullahquhtani4247 Sure will make a video on this one.

[@abdullahquhtani4247]

@@learnssis Thank you soooo much. Highly appreciated 🙏.

[@learnssis]

@@abdullahquhtani4247 no problem.

[@izenish]

Great video sir
but i came across an issue i was tasked with migrating databases
i wrote stored procs and did that but forgot that there were systemed-versioned tables
I have already migrated those tables and now only the history tables need to be migrated (note that systemed versioned table and history table are exactly the same no extra fields or different field)
I tried with an insert statement but it only says "Cannot insert rows in a temporal history table" please help :) been following you since ages

[@learnssis]

Hi, I have not done this before and not sure how this will be done. Can you ask this question on stackverflow.co ? there are a lot of sql experts there ready to help on questions.

[@izenish]

@@learnssis i messaged you on linkedin as well, i came across a post in stackoverflow aswell and they just suggested to use the normal insert statement but it doesnt work on mine for some reason

[@sanjaykumart3563]

How can we get the all the changed values using a query… Example : old salary and new salary?

[@learnssis]

I have shown that in the video, watch the video from 7:35

[@pdamu913]

Thanks bro It was useful for me

[@learnssis]

Glad you found it useful.

[@alperenoz4702]

Helpful, thanks

[@learnssis]

Thank you.

[@supriyareddy4653]

Hii, This information is very good but I have a question and how to know in history table which column can be changed and who modified and when. We can create anay view for this explain this scenario

[@learnssis]

You won't be able to identify like which column was modified after looking at history table. Maybe you can compare the data between history table and main table and see which column value is different. To know who modified them, temporal tables do not automatically provide a way to handle them, for that you would need to a column like ModifiedBy and update it while inserting\updating the data to the table.

[@supriyareddy4653]

@@learnssis thanks for your information. please could you do one video on this how to compare main table and history table

[@vinojack5617]

How to store insert history also in history table bro? I want to store that too.

[@learnssis]

History table contains history data, any data that got updated\deleted from the main table. If you want to store all records those got inserted to main table in history table as well then use the insert trigger and insert the data to history table.

[@MrEshwarprasad]

Aqil Ji can you share a video of how to insert data into the dim date table in SSIS

[@learnssis]

Hi, you can use below sql script
CREATE TABLE [dbo].[DimDate]
( [DateKey] INT primary key,
[Date] DATETIME,
[FullDateUK] CHAR(10), -- Date in dd-MM-yyyy format
[FullDateUSA] CHAR(10),-- Date in MM-dd-yyyy format
[DayOfMonth] VARCHAR(2), -- Field will hold day number of Month
[DaySuffix] VARCHAR(4), -- Apply suffix as 1st, 2nd ,3rd etc
[DayName] VARCHAR(9), -- Contains name of the day, Sunday, Monday
[DayOfWeekUSA] CHAR(1),-- First Day Sunday=1 and Saturday=7
[DayOfWeekUK] CHAR(1),-- First Day Monday=1 and Sunday=7
[DayOfWeekInMonth] VARCHAR(2), --1st Monday or 2nd Monday in Month
[DayOfWeekInYear] VARCHAR(2),
[DayOfQuarter] VARCHAR(3),
[DayOfYear] VARCHAR(3),
[WeekOfMonth] VARCHAR(1),-- Week Number of Month
[WeekOfQuarter] VARCHAR(2), --Week Number of the Quarter
[WeekOfYear] VARCHAR(2),--Week Number of the Year
[Month] VARCHAR(2), --Number of the Month 1 to 12
[MonthName] VARCHAR(9),--January, February etc
[MonthOfQuarter] VARCHAR(2),-- Month Number belongs to Quarter
[Quarter] CHAR(1),
[QuarterName] VARCHAR(9),--First,Second..
[Year] CHAR(4),-- Year value of Date stored in Row
[YearName] CHAR(7), --CY 2012,CY 2013
[MonthYear] CHAR(10), --Jan-2013,Feb-2013
[MMYYYY] CHAR(6),
[FirstDayOfMonth] DATE,
[LastDayOfMonth] DATE,
[FirstDayOfQuarter] DATE,
[LastDayOfQuarter] DATE,
[FirstDayOfYear] DATE,
[LastDayOfYear] DATE,
[IsHolidayUSA] BIT,-- Flag 1=National Holiday, 0-No National Holiday
[IsWeekday] BIT,-- 0=Week End ,1=Week Day
[HolidayUSA] VARCHAR(50),--Name of Holiday in US
[IsHolidayUK] BIT Null,-- Flag 1=National Holiday, 0-No National Holiday
[HolidayUK] VARCHAR(50) Null --Name of Holiday in UK
)
GO
/********************************************************************************************/
--Specify Start Date and End date here
--Value of Start Date Must be Less than Your End Date
DECLARE @StartDate DATETIME = '01/01/2013' --Starting value of Date Range
DECLARE @EndDate DATETIME = '01/01/2021' --End Value of Date Range
--Temporary Variables To Hold the Values During Processing of Each Date of Year
DECLARE
@DayOfWeekInMonth INT,
@DayOfWeekInYear INT,
@DayOfQuarter INT,
@WeekOfMonth INT,
@CurrentYear INT,
@CurrentMonth INT,
@CurrentQuarter INT
/*Table Data type to store the day of week count for the month and year*/
DECLARE @DayOfWeek TABLE (DOW INT, MonthCount INT, QuarterCount INT, YearCount INT)
INSERT INTO @DayOfWeek VALUES (1, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (2, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (3, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (4, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (5, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (6, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (7, 0, 0, 0)
--Extract and assign various parts of Values from Current Date to Variable
DECLARE @CurrentDate AS DATETIME = @StartDate
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
SET @CurrentYear = DATEPART(YY, @CurrentDate)
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
/********************************************************************************************/
--Proceed only if Start Date(Current date ) is less than End date you specified above
WHILE @CurrentDate < @EndDate
BEGIN

/*Begin day of week logic*/
/*Check for Change in Month of the Current date if Month changed then
Change variable value*/
IF @CurrentMonth != DATEPART(MM, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET MonthCount = 0
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
END
/* Check for Change in Quarter of the Current date if Quarter changed then change
Variable value*/
IF @CurrentQuarter != DATEPART(QQ, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET QuarterCount = 0
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
END

/* Check for Change in Year of the Current date if Year changed then change
Variable value*/

IF @CurrentYear != DATEPART(YY, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET YearCount = 0
SET @CurrentYear = DATEPART(YY, @CurrentDate)
END

-- Set values in table data type created above from variables
UPDATE @DayOfWeek
SET
MonthCount = MonthCount + 1,
QuarterCount = QuarterCount + 1,
YearCount = YearCount + 1
WHERE DOW = DATEPART(DW, @CurrentDate)
SELECT
@DayOfWeekInMonth = MonthCount,
@DayOfQuarter = QuarterCount,
@DayOfWeekInYear = YearCount
FROM @DayOfWeek
WHERE DOW = DATEPART(DW, @CurrentDate)

/*End day of week logic*/
/* Populate Your Dimension Table with values*/

INSERT INTO [dbo].[DimDate]
SELECT

CONVERT (char(8),@CurrentDate,112) as DateKey,
@CurrentDate AS Date,
CONVERT (char(10),@CurrentDate,103) as FullDateUK,
CONVERT (char(10),@CurrentDate,101) as FullDateUSA,
DATEPART(DD, @CurrentDate) AS DayOfMonth,
--Apply Suffix values like 1st, 2nd 3rd etc..
CASE
WHEN DATEPART(DD,@CurrentDate) IN (11,12,13)
THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th'
WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 1
THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'st'
WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 2
THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'nd'
WHEN RIGHT(DATEPART(DD,@CurrentDate),1) = 3
THEN CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'rd'
ELSE CAST(DATEPART(DD,@CurrentDate) AS VARCHAR) + 'th'
END AS DaySuffix,

DATENAME(DW, @CurrentDate) AS DayName,
DATEPART(DW, @CurrentDate) AS DayOfWeekUSA,
-- check for day of week as Per US and change it as per UK format
CASE DATEPART(DW, @CurrentDate)
WHEN 1 THEN 7
WHEN 2 THEN 1
WHEN 3 THEN 2
WHEN 4 THEN 3
WHEN 5 THEN 4
WHEN 6 THEN 5
WHEN 7 THEN 6
END
AS DayOfWeekUK,

@DayOfWeekInMonth AS DayOfWeekInMonth,
@DayOfWeekInYear AS DayOfWeekInYear,
@DayOfQuarter AS DayOfQuarter,
DATEPART(DY, @CurrentDate) AS DayOfYear,
DATEPART(WW, @CurrentDate) + 1 - DATEPART(WW, CONVERT(VARCHAR,
DATEPART(MM, @CurrentDate)) + '/1/' + CONVERT(VARCHAR,
DATEPART(YY, @CurrentDate))) AS WeekOfMonth,
(DATEDIFF(DD, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0),
@CurrentDate) / 7) + 1 AS WeekOfQuarter,
DATEPART(WW, @CurrentDate) AS WeekOfYear,
DATEPART(MM, @CurrentDate) AS Month,
DATENAME(MM, @CurrentDate) AS MonthName,
CASE
WHEN DATEPART(MM, @CurrentDate) IN (1, 4, 7, 10) THEN 1
WHEN DATEPART(MM, @CurrentDate) IN (2, 5, 8, 11) THEN 2
WHEN DATEPART(MM, @CurrentDate) IN (3, 6, 9, 12) THEN 3
END AS MonthOfQuarter,
DATEPART(QQ, @CurrentDate) AS Quarter,
CASE DATEPART(QQ, @CurrentDate)
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
END AS QuarterName,
DATEPART(YEAR, @CurrentDate) AS Year,
'CY ' + CONVERT(VARCHAR, DATEPART(YEAR, @CurrentDate)) AS YearName,
LEFT(DATENAME(MM, @CurrentDate), 3) + '-' + CONVERT(VARCHAR,
DATEPART(YY, @CurrentDate)) AS MonthYear,
RIGHT('0' + CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)),2) +
CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS MMYYYY,
CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD,
@CurrentDate) - 1), @CurrentDate))) AS FirstDayOfMonth,
CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD,
(DATEADD(MM, 1, @CurrentDate)))), DATEADD(MM, 1,
@CurrentDate)))) AS LastDayOfMonth,
DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0) AS FirstDayOfQuarter,
DATEADD(QQ, DATEDIFF(QQ, -1, @CurrentDate), -1) AS LastDayOfQuarter,
CONVERT(DATETIME, '01/01/' + CONVERT(VARCHAR, DATEPART(YY,
@CurrentDate))) AS FirstDayOfYear,
CONVERT(DATETIME, '12/31/' + CONVERT(VARCHAR, DATEPART(YY,
@CurrentDate))) AS LastDayOfYear,
NULL AS IsHolidayUSA,
CASE DATEPART(DW, @CurrentDate)
WHEN 1 THEN 0
WHEN 2 THEN 1
WHEN 3 THEN 1
WHEN 4 THEN 1
WHEN 5 THEN 1
WHEN 6 THEN 1
WHEN 7 THEN 0
END AS IsWeekday,
NULL AS HolidayUSA, Null, Null
SET @CurrentDate = DATEADD(DD, 1, @CurrentDate)
END
/********************************************************************************************/
go
SELECT * FROM [dbo].[DimDate]

[@learnssis]

Sure, I will make a video on this one. Below is the updated script to do the task
IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'dbo' AND TABLE_NAME = 'DimDate'))
BEGIN
DROP TABLE DimDate
END
go
GO
CREATE TABLE [dbo].[DimDate]
( [DateKey] INT primary key,
[Date] DATETIME,
[FullDate] CHAR(10),-- Date in MM-dd-yyyy format
[DayOfMonth] VARCHAR(2), -- Field will hold day number of Month
[DayName] VARCHAR(9), -- Contains name of the day, Sunday, Monday
[DayOfWeek] CHAR(1),-- First Day Sunday=1 and Saturday=7
[DayOfWeekInMonth] VARCHAR(2), --1st Monday or 2nd Monday in Month
[DayOfWeekInYear] VARCHAR(2),
[DayOfQuarter] VARCHAR(3),
[DayOfYear] VARCHAR(3),
[WeekOfMonth] VARCHAR(1),-- Week Number of Month
[WeekOfQuarter] VARCHAR(2), --Week Number of the Quarter
[WeekOfYear] VARCHAR(2),--Week Number of the Year
[Month] VARCHAR(2), --Number of the Month 1 to 12
[MonthName] VARCHAR(9),--January, February etc
[MonthOfQuarter] VARCHAR(2),-- Month Number belongs to Quarter
[Quarter] CHAR(1),
[QuarterName] VARCHAR(9),--First,Second..
[Year] CHAR(4),-- Year value of Date stored in Row
[YearName] CHAR(7), --CY 2012,CY 2013
[MonthYear] CHAR(10), --Jan-2013,Feb-2013
[MMYYYY] CHAR(6),
[FirstDayOfMonth] DATE,
[LastDayOfMonth] DATE,
[FirstDayOfQuarter] DATE,
[LastDayOfQuarter] DATE,
[FirstDayOfYear] DATE,
[LastDayOfYear] DATE
)
GO
/********************************************************************************************/
--Specify Start Date and End date here
--Value of Start Date Must be Less than Your End Date
DECLARE @StartDate DATETIME = '01/01/2010' --Starting value of Date Range
DECLARE @EndDate DATETIME = '01/01/2021' --End Value of Date Range
--Temporary Variables To Hold the Values During Processing of Each Date of Year
DECLARE
@DayOfWeekInMonth INT,
@DayOfWeekInYear INT,
@DayOfQuarter INT,
@WeekOfMonth INT,
@CurrentYear INT,
@CurrentMonth INT,
@CurrentQuarter INT
/*Table Data type to store the day of week count for the month and year*/
DECLARE @DayOfWeek TABLE (DOW INT, MonthCount INT, QuarterCount INT, YearCount INT)
INSERT INTO @DayOfWeek VALUES (1, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (2, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (3, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (4, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (5, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (6, 0, 0, 0)
INSERT INTO @DayOfWeek VALUES (7, 0, 0, 0)
--Extract and assign various parts of Values from Current Date to Variable
DECLARE @CurrentDate AS DATETIME = @StartDate
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
SET @CurrentYear = DATEPART(YY, @CurrentDate)
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
/********************************************************************************************/
--Proceed only if Start Date(Current date ) is less than End date you specified above
WHILE @CurrentDate < @EndDate
BEGIN

/*Begin day of week logic*/
/*Check for Change in Month of the Current date if Month changed then
Change variable value*/
IF @CurrentMonth != DATEPART(MM, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET MonthCount = 0
SET @CurrentMonth = DATEPART(MM, @CurrentDate)
END
/* Check for Change in Quarter of the Current date if Quarter changed then change
Variable value*/
IF @CurrentQuarter != DATEPART(QQ, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET QuarterCount = 0
SET @CurrentQuarter = DATEPART(QQ, @CurrentDate)
END

/* Check for Change in Year of the Current date if Year changed then change
Variable value*/

IF @CurrentYear != DATEPART(YY, @CurrentDate)
BEGIN
UPDATE @DayOfWeek
SET YearCount = 0
SET @CurrentYear = DATEPART(YY, @CurrentDate)
END

-- Set values in table data type created above from variables
UPDATE @DayOfWeek
SET
MonthCount = MonthCount + 1,
QuarterCount = QuarterCount + 1,
YearCount = YearCount + 1
WHERE DOW = DATEPART(DW, @CurrentDate)
SELECT
@DayOfWeekInMonth = MonthCount,
@DayOfQuarter = QuarterCount,
@DayOfWeekInYear = YearCount
FROM @DayOfWeek
WHERE DOW = DATEPART(DW, @CurrentDate)

/*End day of week logic*/
/* Populate Your Dimension Table with values*/

INSERT INTO [dbo].[DimDate]
SELECT

CONVERT (char(8),@CurrentDate,112) as DateKey,
@CurrentDate AS Date,
CONVERT (char(10),@CurrentDate,101) as FullDate,
DATEPART(DD, @CurrentDate) AS DayOfMonth,
DATENAME(DW, @CurrentDate) AS DayName,
DATEPART(DW, @CurrentDate) AS DayOfWeek,
@DayOfWeekInMonth AS DayOfWeekInMonth,
@DayOfWeekInYear AS DayOfWeekInYear,
@DayOfQuarter AS DayOfQuarter,
DATEPART(DY, @CurrentDate) AS DayOfYear,
DATEPART(WW, @CurrentDate) + 1 - DATEPART(WW, CONVERT(VARCHAR,
DATEPART(MM, @CurrentDate)) + '/1/' + CONVERT(VARCHAR,
DATEPART(YY, @CurrentDate))) AS WeekOfMonth,
(DATEDIFF(DD, DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0),
@CurrentDate) / 7) + 1 AS WeekOfQuarter,
DATEPART(WW, @CurrentDate) AS WeekOfYear,
DATEPART(MM, @CurrentDate) AS Month,
DATENAME(MM, @CurrentDate) AS MonthName,
CASE
WHEN DATEPART(MM, @CurrentDate) IN (1, 4, 7, 10) THEN 1
WHEN DATEPART(MM, @CurrentDate) IN (2, 5, 8, 11) THEN 2
WHEN DATEPART(MM, @CurrentDate) IN (3, 6, 9, 12) THEN 3
END AS MonthOfQuarter,
DATEPART(QQ, @CurrentDate) AS Quarter,
CASE DATEPART(QQ, @CurrentDate)
WHEN 1 THEN 'First'
WHEN 2 THEN 'Second'
WHEN 3 THEN 'Third'
WHEN 4 THEN 'Fourth'
END AS QuarterName,
DATEPART(YEAR, @CurrentDate) AS Year,
'CY ' + CONVERT(VARCHAR, DATEPART(YEAR, @CurrentDate)) AS YearName,
LEFT(DATENAME(MM, @CurrentDate), 3) + '-' + CONVERT(VARCHAR,
DATEPART(YY, @CurrentDate)) AS MonthYear,
RIGHT('0' + CONVERT(VARCHAR, DATEPART(MM, @CurrentDate)),2) +
CONVERT(VARCHAR, DATEPART(YY, @CurrentDate)) AS MMYYYY,
CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD,
@CurrentDate) - 1), @CurrentDate))) AS FirstDayOfMonth,
CONVERT(DATETIME, CONVERT(DATE, DATEADD(DD, - (DATEPART(DD,
(DATEADD(MM, 1, @CurrentDate)))), DATEADD(MM, 1,
@CurrentDate)))) AS LastDayOfMonth,
DATEADD(QQ, DATEDIFF(QQ, 0, @CurrentDate), 0) AS FirstDayOfQuarter,
DATEADD(QQ, DATEDIFF(QQ, -1, @CurrentDate), -1) AS LastDayOfQuarter,
CONVERT(DATETIME, '01/01/' + CONVERT(VARCHAR, DATEPART(YY,
@CurrentDate))) AS FirstDayOfYear,
CONVERT(DATETIME, '12/31/' + CONVERT(VARCHAR, DATEPART(YY,
@CurrentDate))) AS LastDayOfYear
SET @CurrentDate = DATEADD(DD, 1, @CurrentDate)
END
/********************************************************************************************/
go
SELECT * FROM [dbo].[DimDate]

[@Confidential007.]

This is too much helpful video

[@learnssis]

Thank you Virendra. Its good to know that you liked the video.