Search code examples
sqlsql-serverssisetldata-warehouse

Populate surrogate Datekey based on Date in a column

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?

Solution

  • 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.