Facing error during query execution I wrote a query in which I want to merge two tables and also create a log table for the data.



as you can see at first I declared the @DimvisitorLog. This is for data log
I checked all type of fields and the are all ok. I mean there is nothing wrong with matching datatypes but I face this error:
I would guess that you need to also insert the visitorcode; and your spacing looks weird in the image.

merge SalesTrainingDW.dbo.Visitor      as target
  using SalesTrainingStage.dbo.Visitor as source
     on   target.VisitorCode =  source.VisitorCode 
      and target.VisitorName &lt;&gt; source.VisitorName
      when not matched then insert (VisitorCode, VisitorName)
        values (source.VisitorCode, source.VisitorName)
      --output $action, deleted.VisitorCode, deleted.VisitorName --changed to reflect M. Ali's point 
      output $action, inserted.VisitorCode, inserted.VisitorName 
        into @DimVisitorLog (ActionX, VisitorCode, VisitorName);

insert into SalesTrainingDW.dbo.VisitorLOG (VisitorCode, VisitorName, ActionX, stamp)
select VisitorCode, VisitorName, Actionx, stamp=getdate()
  from @DimVisitorLog 
  where ActionX = 'insert';  --changed to reflect M. Ali's point 


You can also skip the table variable like this:

insert into SalesTrainingDW.dbo.VisitorLOG (VisitorCode, VisitorName, ActionX, stamp)
  select VisitorCode, VisitorName, ActionX, stamp=getdate()
  from (
    merge SalesTrainingDW.dbo.Visitor      as target
      using SalesTrainingStage.dbo.Visitor as source
         on   target.VisitorCode =  source.VisitorCode 
          and target.VisitorName &lt;&gt; source.VisitorName
          when not matched then insert (VisitorCode, VisitorName)
            values (source.VisitorCode, source.VisitorName)
          --output $action, deleted.VisitorCode, deleted.VisitorName --changed to reflect M. Ali's point 
          output $action, inserted.VisitorCode, inserted.VisitorName
      ) as i (ActionX, VisitorCode, VisitorName)
      where ActionX = 'insert'


Since this operation is only inserting values, you can skip merge and do an insert. Unless you are going to use the extra options merge gives you on output:

insert SalesTrainingDW.dbo.Visitor (VisitorCode, VisitorName)
  output ActionX='insert', inserted.VisitorCode, inserted.VisitorName, stamp=getdate()
    into @DimVisitorLog (ActionX, VisitorCode, VisitorName, stamp)
  select  VisitorCode, VisitorName
    from  SalesTrainingStage.dbo.Visitor s
      where not exists ()
        select 1 
          from SalesTrainingDW.dbo.Visitor v 
          where s.visitorcode=v.visitorcode 
          and s.visitorname=v.visitorname
          );


or with a left join instead of not exists:

insert SalesTrainingDW.dbo.Visitor (VisitorCode, VisitorName)
  output ActionX='insert', inserted.VisitorCode, inserted.VisitorName, stamp=getdate()
    into @DimVisitorLog (ActionX, VisitorCode, VisitorName, stamp)
  select  VisitorCode, VisitorName
    from  SalesTrainingStage.dbo.Visitor s
      left join SalesTrainingDW.dbo.Visitor v on s.visitorcode=v.visitorcode and s.visitorname=v.visitorname
    where v.visitorcode is null;


and you could also skip the table variable with either of those inserts:

insert into SalesTrainingDW.dbo.VisitorLOG (VisitorCode, VisitorName, ActionX, stamp)
  select i.VisitorCode, i.VisitorName,  ActionX = 'insert', stamp=getdate()
  from (
    insert SalesTrainingDW.dbo.Visitor (VisitorCode, VisitorName)
      output inserted.VisitorCode, inserted.VisitorName
      select  VisitorCode, VisitorName
        from  SalesTrainingStage.dbo.Visitor s
        where not exists (
          select 1 
            from SalesTrainingDW.dbo.Visitor v 
            where s.visitorcode=v.visitorcode 
              and s.visitorname=v.visitorname
              )
  ) as i;