Search code examples
sqlcastingdata-warehouse

Create A dateKey Column and save the result to the table

I want to add a date key to my fact tables, so I wrote the statement but I couldn't do the insert step. How Can I insert the result to my existing table?

  SELECT  top (1000)
   [TransDate]
  ,[Store]
  ,[StoreId]
  ,[TillNo]
  ,[TransId]
  ,[LineNo]
  ,[Barcode]
  ,[Quantity]
  ,[NetAmount]
  ,[UnitPrice]
  ,[CostAmount]
  ,[TransType]
  ,[Discount]
  ,CAST (CONVERT(nvarchar(8),[TransDate] ,112) AS int) AS [TransDateKey]
  FROM [int].[FACT_SalesDetail]

I created a column in the sales detail table named TransDateKey, I want to insert the cast result into it.

thanks in advance.

Solution

  • If I understand you correctly, you can simply use an UPDATE statement for this:

    UPDATE [int].[FACT_SalesDetail]
SET [TransDateKey] = CAST(CONVERT(NVARCHAR(8), [TransDate], 112) AS INT)