question

tlenzmeier avatar image
tlenzmeier asked

Merge Statement Error - SCD Type 2

Hello, Can someone help me figure out what column(s) are missing? I am either blind or missing something obvious. The error I get in SSMS is "Msg 8159, Level 16, State 1 Line 206 'changes has fewer columns that were specified in the column list." Here's the statement: --============================================================================= -- begin of insert using merge --============================================================================= insert into [DataWarehouse].[dim].[SubcontractChangeOrder] ( CompanyCode , LegalEntityKey , CustomerCode , CustomerKey , JobNumber , ProjectKey , ChangeRequestNumber , VendorCode , VendorKey , SubcontractNumber , SubcontractKey , Description , OriginationDate , Division , DivisionKey , PricingStatus , SubcontractAmount , CostType , BidItemCode , PhaseCode , DimensionCheckSum ,RowEffectiveDate ,CurrentRecordInd ) -- Select the rows/columns to insert that are output from this merge statement -- In this example, the rows to be inserted are the rows that have changed (UPDATE). select CompanyCode ,LegalEntityKey ,CustomerCode ,CustomerKey ,JobNumber ,ProjectKey ,ChangeRequestNumber ,VendorCode ,VendorKey ,SubcontractNumber ,SubcontractKey ,[Description] ,OriginationDate ,Division ,DivisionKey ,PricingStatus ,SubcontractAmount ,CostType ,BidItemCode ,PhaseCode ,DimensionCheckSum ,RowEffectiveDate ,CurrentRecordInd from ( -- This is the beginning of the merge statement. -- The target must be defined merge into dim.SubcontractChangeOrder as target using ( -- The source is made up of the attribute columns from the staging table select CompanyCode ,LegalEntityKey ,CustomerCode ,CustomerKey ,JobNumber ,ProjectKey ,ChangeRequestNumber ,VendorCode ,VendorKey ,SubcontractNumber ,SubcontractKey ,[Description] ,OriginationDate ,Division ,DivisionKey ,PricingStatus ,SubcontractAmount ,CostType ,BidItemCode ,PhaseCode ,DimensionCheckSum from [DataWarehouseStaging].[dbo].[Stg_SCD_SubcontractChangeOrder] ) as Source ( CompanyCode ,LegalEntityKey ,CustomerCode ,CustomerKey ,JobNumber ,ProjectKey ,ChangeRequestNumber ,VendorCode ,VendorKey ,SubcontractNumber ,SubcontractKey ,[Description] ,OriginationDate ,Division ,DivisionKey ,PricingStatus ,SubcontractAmount ,CostType ,BidItemCode ,PhaseCode ,DimensionCheckSum ) on ( target.CompanyCode = Source.CompanyCode and target.JobNumber = Source.JobNumber and target.SubcontractNumber = Source.SubcontractNumber and target.ChangeRequestNumber = Source.ChangeRequestNumber and target.VendorCode = Source.VendorCode ) -- If the ID's match but the CheckSums are different, then the record has changed; -- therefore, update the existing record in the target, end dating the record -- and set the CurrentRecord flag to N when matched and target.DimensionCheckSum <> Source.DimensionCheckSum and target.CurrentRecordInd = 1 then update set target.RowEndDate = getdate() ,target.CurrentRecordInd = 0 -- If the ID's do not match, then the record is new; -- therefore, insert the new record into the target using the values from the source. when not matched then insert ( CompanyCode ,LegalEntityKey ,CustomerCode ,CustomerKey ,JobNumber ,ProjectKey ,ChangeRequestNumber ,VendorCode ,VendorKey ,SubcontractNumber ,SubcontractKey ,[Description] ,OriginationDate ,Division ,DivisionKey ,PricingStatus ,SubcontractAmount ,CostType ,BidItemCode ,PhaseCode ,DimensionCheckSum ) values ( source.CompanyCode ,source.LegalEntityKey ,source.CustomerCode ,source.CustomerKey ,source.JobNumber ,source.ProjectKey ,source.ChangeRequestNumber ,source.VendorCode ,source.VendorKey ,source.SubcontractNumber ,source.SubcontractKey ,source.[Description] ,source.OriginationDate ,source.Division ,source.DivisionKey ,source.PricingStatus ,source.SubcontractAmount ,source.CostType ,source.BidItemCode ,source.PhaseCode ,source.DimensionCheckSum ) output $action ,source.CompanyCode ,source.LegalEntityKey ,source.CustomerCode ,source.CustomerKey ,source.JobNumber ,source.ProjectKey ,source.ChangeRequestNumber ,source.VendorCode ,source.VendorKey ,source.SubcontractNumber ,source.SubcontractKey ,source.[Description] ,source.OriginationDate ,source.Division ,source.DivisionKey ,source.PricingStatus ,source.SubcontractAmount ,source.CostType ,source.BidItemCode ,source.PhaseCode ,source.DimensionCheckSum ) as changes -- the end of the merge statement --The changes output below are the records that have changed and will need --to be inserted into the slowly changing dimension. ( action ,CompanyCode ,LegalEntityKey ,CustomerCode ,CustomerKey ,JobNumber ,ProjectKey ,ChangeRequestNumber ,VendorCode ,VendorKey ,SubcontractNumber ,SubcontractKey ,[Description] ,OriginationDate ,Division ,DivisionKey ,PricingStatus ,SubcontractAmount ,CostType ,BidItemCode ,PhaseCode ,DimensionCheckSum ,RowEffectiveDate ,CurrentRecordInd ) where action = 'UPDATE'
t-sqlsql servermerge
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

0 Answers

·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.