SCD Type 2 and Insert Error with Identity Column

I am having trouble inserting records in my data warehouse tables. I have a surrogate key that is created auto increments by 1 for each record. I get the error message:

An explicit value for the identity column in table 'TARGET' can only be specified when a column list is used and IDENTITY_INSERT is ON

Code snippet below:

INSERT INTO [DW_Table] (Valid_Start_Date, Valid_End_Date, Current_Flag, Col1, Col2, Col3) 
  SELECT
   Getdate() AS Valid_Start_Date, NULL AS Valid_End_Date, 1 as Current_Flag, Col1, Col2,Col3
  FROM (
    MERGE INTO [DW_Table] AS TARGET
    USING ([Base_Table]) AS SOURCE
    ON ( SOURCEC.Account_Key = TARGET.Account_Key
         AND TARGET.Current_Flag = 1)
    WHEN MATCHED .................................

I have tried listing out the fields, as I know the issue lies with trying to insert records to the the auto incrementing field. I have done this as part of the "USING" base table line, but I still get errors.

Can someone please give me some pointers please?

Thanks

Solution

Try something like this:

use tempdb
go
drop table if exists dw_table
drop table if exists dw_table_stg 

create table DW_Table
(
  Id int identity, 
  Valid_Start_Date date, 
  Valid_End_Date date, 
  Current_Flag bit, 
  Account_Key int , 
  constraint uk_account_current unique (Account_Key, Current_Flag),
  Col1 int, 
  Col2 int, 
  Col3 int
)

insert into DW_Table (Account_Key,Col1, Col2, Col3, Current_Flag) 
       values (1,2,3,4,1),(2,2,3,4,1),(3,2,3,4,1)

go


declare @DW_Table_stg table( Account_Key int primary key, Col1 int, Col2 int, Col3 int)

insert into @DW_Table_stg (Account_Key,Col1, Col2, Col3) values (1,2,5,12)

insert into [DW_Table] 
     ( Account_Key, Valid_Start_Date, Valid_End_Date, Current_Flag, Col1, Col2, Col3) 
select Account_Key, Getdate(),        NULL,           1,            Col1, Col2, Col3
from 
(
    merge into [DW_Table] AS t
    using @DW_Table_stg AS s
    ON ( s.Account_Key = t.Account_Key )
    when matched and t.Current_Flag = 1 and checksum(s.Col1,s.Col2,s.Col3) <> checksum(t.Col1,t.Col2,t.Col3)
         then update set t.Current_flag = 0, t.Valid_End_Date = getdate()
    when not matched 
         then insert  (Valid_Start_Date, Valid_End_Date, Current_Flag,Account_Key,    Col1,   Col2,   Col3) 
         values       (getdate(),        null,           1,           s.Account_Key,s.Col1, s.Col2, s.Col3)
    output $action [ActionType], s.*
) d
where [ActionType] = 'UPDATE'

select *
from DW_Table
order by current_flag desc, Account_key