sql - Populate surrogate Datekey based on Date in a column

Question

I have created a table

CREATE TABLE myTable(
DateID INT PRIMARY KEY NOT NULL,
myDate DATE)

Then I want to populate the table with data from a staging table using SSIS. The problem is that I don't understand how to generate DateID based on the incoming value from a staging table. For example: after the staging table inserted 2020-12-12, I want my DateID to become 20201212 and so on.

I tried to google this issue but didn't find anything related to my case. (But I don't deny that I did it badly). How can I do it?

question from:https://stackoverflow.com/questions/65601231/populate-surrogate-datekey-based-on-date-in-a-column

Answer 1

You can use a persisted computed column:

CREATE TABLE myTable (
     DateID AS (YEAR(myDate) * 10000 + MONTH(myDate) * 100 + DAY(myDate)) PERSISTED PRIMARY KEY,
     myDate DATE
);

Here is a db<>fiddle.