Problem with merge and multiple columns

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Problem with merge and multiple columns

Author

Topic

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2012-03-23 : 11:07:23

I can't get the following to work. The script finishes without any error but the target table is not updated.

For stock code ##PC_A1 the AbcClass is the only thing different between the source and target tables but it doesn't get updated. Source AbcClass is "D" and target is "A". If I'm reading the output correctly it says the target was updated and the record with "D" was inserted and the record with "A" was deleted.

BEGIN TRAN;
MERGE InterConnectDW.dbo.dimStockCode AS Target
USING 
(
SELECT [StockCode]
      ,[Description]
      ,[LongDesc]
      ,[AlternateKey1]
      ,[AlternateKey2]
      ,[StockUom]
      ,[AlternateUom]
      ,[OtherUom]
      ,[ConvFactAltUom]
      ,[ConvMulDiv]
      ,[ConvFactOthUom]
      ,[MulDiv]
      ,[Decimals]
      ,[Supplier]
      ,[CycleCount]
      ,[ProductClass]
      ,[SerialMethod]
      ,[KitType]
      ,[LowLevelCode]
      ,[Buyer]
      ,[TraceableType]
      ,[BulkIssueFlag]
      ,[AbcClass]
      ,[LeadTime]
      ,[SupercessionDate]
      ,[CostUom]
      ,[PartCategory]
      ,[WarehouseToUse]
      ,[BuyingRule]
      ,[Ebq]
      ,[FixTimePeriod]
      ,[PanSize]
      ,[DockToStock]
      ,[ShelfLife]
      ,[Version]
      ,[Release]
      ,[DemandTimeFence]
      ,[MakeToOrderFlag]
      ,[ManufLeadTime]
      ,[GrossReqRule]
      ,[WipCtlGlCode]
      ,[ResourceCode]
      ,[UserField1]
      ,[UserField2]
      ,[UserField3]
      ,[UserField4]
      ,[UserField5]
      ,[IssMultLotsFlag]
      ,[StockOnHold]
      ,[StockOnHoldReason]
      ,[EccFlag]
      ,[JobsOnHold]
      ,[JobHoldAllocs]
      ,[PurchOnHold]
      ,[SalesOnHold]
      ,[JobClassification]
      ,[DateStkAdded]
      ,[InspectionFlag]
FROM SysproCompany1.dbo.InvMaster
WHERE StockCode = '##PC_A1                       '  --Added to limit test to a single stock code
EXCEPT
SELECT [StockCode]
      ,[Description]
      ,[LongDesc]
      ,[AlternateKey1]
      ,[AlternateKey2]
      ,[StockUom]
      ,[AlternateUom]
      ,[OtherUom]
      ,[ConvFactAltUom]
      ,[ConvMulDiv]
      ,[ConvFactOthUom]
      ,[MulDiv]
      ,[Decimals]
      ,[Supplier]
      ,[CycleCount]
      ,[ProductClass]
      ,[SerialMethod]
      ,[KitType]
      ,[LowLevelCode]
      ,[Buyer]
      ,[TraceableType]
      ,[BulkIssueFlag]
      ,[AbcClass]
      ,[LeadTime]
      ,[SupercessionDate]
      ,[CostUom]
      ,[PartCategory]
      ,[WarehouseToUse]
      ,[BuyingRule]
      ,[Ebq]
      ,[FixTimePeriod]
      ,[PanSize]
      ,[DockToStock]
      ,[ShelfLife]
      ,[Version]
      ,[Release]
      ,[DemandTimeFence]
      ,[MakeToOrderFlag]
      ,[ManufLeadTime]
      ,[GrossReqRule]
      ,[WipCtlGlCode]
      ,[ResourceCode]
      ,[UserField1]
      ,[UserField2]
      ,[UserField3]
      ,[UserField4]
      ,[UserField5]
      ,[IssMultLotsFlag]
      ,[StockOnHold]
      ,[StockOnHoldReason]
      ,[EccFlag]
      ,[JobsOnHold]
      ,[JobHoldAllocs]
      ,[PurchOnHold]
      ,[SalesOnHold]
      ,[JobClassification]
      ,[DateStkAdded]
      ,[InspectionFlag]
FROM InterConnectDW.dbo.dimStockCode
) AS Source
ON (Target.StockCode = Source.StockCode)
WHEN MATCHED 
AND 
(
	   Target.[Description] <> Source.[Description]
	OR Target.[LongDesc] <> Source.[LongDesc]
	OR Target.[AlternateKey1] <> Source.[AlternateKey1]
	OR Target.[AlternateKey2] <> Source.[AlternateKey2]
	OR Target.[StockUom] <> Source.[StockUom]
	OR Target.[AlternateUom] <> Source.[AlternateUom]
	OR Target.[OtherUom] <> Source.[OtherUom]
	OR Target.[ConvFactAltUom] <> Source.[ConvFactAltUom]
	OR Target.[ConvMulDiv] <> Source.[ConvMulDiv]
	OR Target.[ConvFactOthUom] <> Source.[ConvFactOthUom]
	OR Target.[MulDiv] <> Source.[MulDiv]
	OR Target.[Decimals] <> Source.[Decimals]
	OR Target.[Supplier] <> Source.[Supplier]
	OR Target.[CycleCount] <> Source.[CycleCount]
	OR Target.[ProductClass] <> Source.[ProductClass]
	OR Target.[SerialMethod] <> Source.[SerialMethod]
	OR Target.[KitType] <> Source.[KitType]
	OR Target.[LowLevelCode] <> Source.[LowLevelCode]
	OR Target.[Buyer] <> Source.[Buyer]
	OR Target.[TraceableType] <> Source.[TraceableType]
	OR Target.[BulkIssueFlag] <> Source.[BulkIssueFlag]
	OR Target.[AbcClass] <> Source.[AbcClass]
	OR Target.[LeadTime] <> Source.[LeadTime]
	OR Target.[SupercessionDate] <> Source.[SupercessionDate]
	OR Target.[CostUom] <> Source.[CostUom]
	OR Target.[PartCategory] <> Source.[PartCategory]
	OR Target.[WarehouseToUse] <> Source.[WarehouseToUse]
	OR Target.[BuyingRule] <> Source.[BuyingRule]
	OR Target.[Ebq] <> Source.[Ebq]
	OR Target.[FixTimePeriod] <> Source.[FixTimePeriod]
	OR Target.[PanSize] <> Source.[PanSize]
	OR Target.[DockToStock] <> Source.[DockToStock]
	OR Target.[ShelfLife] <> Source.[ShelfLife]
	OR Target.[Version] <> Source.[Version]
	OR Target.[Release] <> Source.[Release]
	OR Target.[DemandTimeFence] <> Source.[DemandTimeFence]
	OR Target.[MakeToOrderFlag] <> Source.[MakeToOrderFlag]
	OR Target.[ManufLeadTime] <> Source.[ManufLeadTime]
	OR Target.[GrossReqRule] <> Source.[GrossReqRule]
	OR Target.[WipCtlGlCode] <> Source.[WipCtlGlCode]
	OR Target.[ResourceCode] <> Source.[ResourceCode]
	OR Target.[UserField1] <> Source.[UserField1]
	OR Target.[UserField2] <> Source.[UserField2]
	OR Target.[UserField3] <> Source.[UserField3]
	OR Target.[UserField4] <> Source.[UserField4]
	OR Target.[UserField5] <> Source.[UserField5]
	OR Target.[IssMultLotsFlag] <> Source.[IssMultLotsFlag]
	OR Target.[StockOnHold] <> Source.[StockOnHold]
	OR Target.[StockOnHoldReason] <> Source.[StockOnHoldReason]
	OR Target.[EccFlag] <> Source.[EccFlag]
	OR Target.[JobsOnHold] <> Source.[JobsOnHold]
	OR Target.[JobHoldAllocs] <> Source.[JobHoldAllocs]
	OR Target.[PurchOnHold] <> Source.[PurchOnHold]
	OR Target.[SalesOnHold] <> Source.[SalesOnHold]
	OR Target.[JobClassification] <> Source.[JobClassification]
	OR Target.[DateStkAdded] <> Source.[DateStkAdded]
	OR Target.[InspectionFlag] <> Source.[InspectionFlag]
)
THEN
    UPDATE SET Target.[Description] = Source.[Description]
	,Target.[LongDesc] = Source.[LongDesc]
	,Target.[AlternateKey1] = Source.[AlternateKey1]
	,Target.[AlternateKey2] = Source.[AlternateKey2]
	,Target.[StockUom] = Source.[StockUom]
	,Target.[AlternateUom] = Source.[AlternateUom]
	,Target.[OtherUom] = Source.[OtherUom]
	,Target.[ConvFactAltUom] = Source.[ConvFactAltUom]
	,Target.[ConvMulDiv] = Source.[ConvMulDiv]
	,Target.[ConvFactOthUom] = Source.[ConvFactOthUom]
	,Target.[MulDiv] = Source.[MulDiv]
	,Target.[Decimals] = Source.[Decimals]
	,Target.[Supplier] = Source.[Supplier]
	,Target.[CycleCount] = Source.[CycleCount]
	,Target.[ProductClass] = Source.[ProductClass]
	,Target.[SerialMethod] = Source.[SerialMethod]
	,Target.[KitType] = Source.[KitType]
	,Target.[LowLevelCode] = Source.[LowLevelCode]
	,Target.[Buyer] = Source.[Buyer]
	,Target.[TraceableType] = Source.[TraceableType]
	,Target.[BulkIssueFlag] = Source.[BulkIssueFlag]
	,Target.[AbcClass] = Source.[AbcClass]
	,Target.[LeadTime] = Source.[LeadTime]
	,Target.[SupercessionDate] = Source.[SupercessionDate]
	,Target.[CostUom] = Source.[CostUom]
	,Target.[PartCategory] = Source.[PartCategory]
	,Target.[WarehouseToUse] = Source.[WarehouseToUse]
	,Target.[BuyingRule] = Source.[BuyingRule]
	,Target.[Ebq] = Source.[Ebq]
	,Target.[FixTimePeriod] = Source.[FixTimePeriod]
	,Target.[PanSize] = Source.[PanSize]
	,Target.[DockToStock] = Source.[DockToStock]
	,Target.[ShelfLife] = Source.[ShelfLife]
	,Target.[Version] = Source.[Version]
	,Target.[Release] = Source.[Release]
	,Target.[DemandTimeFence] = Source.[DemandTimeFence]
	,Target.[MakeToOrderFlag] = Source.[MakeToOrderFlag]
	,Target.[ManufLeadTime] = Source.[ManufLeadTime]
	,Target.[GrossReqRule] = Source.[GrossReqRule]
	,Target.[WipCtlGlCode] = Source.[WipCtlGlCode]
	,Target.[ResourceCode] = Source.[ResourceCode]
	,Target.[UserField1] = Source.[UserField1]
	,Target.[UserField2] = Source.[UserField2]
	,Target.[UserField3] = Source.[UserField3]
	,Target.[UserField4] = Source.[UserField4]
	,Target.[UserField5] = Source.[UserField5]
	,Target.[IssMultLotsFlag] = Source.[IssMultLotsFlag]
	,Target.[StockOnHold] = Source.[StockOnHold]
	,Target.[StockOnHoldReason] = Source.[StockOnHoldReason]
	,Target.[EccFlag] = Source.[EccFlag]
	,Target.[JobsOnHold] = Source.[JobsOnHold]
	,Target.[JobHoldAllocs] = Source.[JobHoldAllocs]
	,Target.[PurchOnHold] = Source.[PurchOnHold]
	,Target.[SalesOnHold] = Source.[SalesOnHold]
	,Target.[JobClassification] = Source.[JobClassification]
	,Target.[DateStkAdded] = Source.[DateStkAdded]
	,Target.[InspectionFlag] = Source.[InspectionFlag]
WHEN NOT MATCHED BY TARGET THEN
    INSERT
    (
	   [StockCode]
	  ,[Description]
	  ,[LongDesc]
	  ,[AlternateKey1]
	  ,[AlternateKey2]
	  ,[StockUom]
	  ,[AlternateUom]
	  ,[OtherUom]
	  ,[ConvFactAltUom]
	  ,[ConvMulDiv]
	  ,[ConvFactOthUom]
	  ,[MulDiv]
	  ,[Decimals]
	  ,[Supplier]
	  ,[CycleCount]
	  ,[ProductClass]
	  ,[SerialMethod]
	  ,[KitType]
	  ,[LowLevelCode]
	  ,[Buyer]
	  ,[TraceableType]
	  ,[BulkIssueFlag]
	  ,[AbcClass]
	  ,[LeadTime]
	  ,[SupercessionDate]
	  ,[CostUom]
	  ,[PartCategory]
	  ,[WarehouseToUse]
	  ,[BuyingRule]
	  ,[Ebq]
	  ,[FixTimePeriod]
	  ,[PanSize]
	  ,[DockToStock]
	  ,[ShelfLife]
	  ,[Version]
	  ,[Release]
	  ,[DemandTimeFence]
	  ,[MakeToOrderFlag]
	  ,[ManufLeadTime]
	  ,[GrossReqRule]
	  ,[WipCtlGlCode]
	  ,[ResourceCode]
	  ,[UserField1]
	  ,[UserField2]
	  ,[UserField3]
	  ,[UserField4]
	  ,[UserField5]
	  ,[IssMultLotsFlag]
	  ,[StockOnHold]
	  ,[StockOnHoldReason]
	  ,[EccFlag]
	  ,[JobsOnHold]
	  ,[JobHoldAllocs]
	  ,[PurchOnHold]
	  ,[SalesOnHold]
	  ,[JobClassification]
	  ,[DateStkAdded]
	  ,[InspectionFlag]
    )
    VALUES
    (
       Source.[StockCode]
      ,Source.[Description]
      ,Source.[LongDesc]
      ,Source.[AlternateKey1]
      ,Source.[AlternateKey2]
      ,Source.[StockUom]
      ,Source.[AlternateUom]
      ,Source.[OtherUom]
      ,Source.[ConvFactAltUom]
      ,Source.[ConvMulDiv]
      ,Source.[ConvFactOthUom]
      ,Source.[MulDiv]
      ,Source.[Decimals]
      ,Source.[Supplier]
      ,Source.[CycleCount]
      ,Source.[ProductClass]
      ,Source.[SerialMethod]
      ,Source.[KitType]
      ,Source.[LowLevelCode]
      ,Source.[Buyer]
      ,Source.[TraceableType]
      ,Source.[BulkIssueFlag]
      ,Source.[AbcClass]
      ,Source.[LeadTime]
      ,Source.[SupercessionDate]
      ,Source.[CostUom]
      ,Source.[PartCategory]
      ,Source.[WarehouseToUse]
      ,Source.[BuyingRule]
      ,Source.[Ebq]
      ,Source.[FixTimePeriod]
      ,Source.[PanSize]
      ,Source.[DockToStock]
      ,Source.[ShelfLife]
      ,Source.[Version]
      ,Source.[Release]
      ,Source.[DemandTimeFence]
      ,Source.[MakeToOrderFlag]
      ,Source.[ManufLeadTime]
      ,Source.[GrossReqRule]
      ,Source.[WipCtlGlCode]
      ,Source.[ResourceCode]
      ,Source.[UserField1]
      ,Source.[UserField2]
      ,Source.[UserField3]
      ,Source.[UserField4]
      ,Source.[UserField5]
      ,Source.[IssMultLotsFlag]
      ,Source.[StockOnHold]
      ,Source.[StockOnHoldReason]
      ,Source.[EccFlag]
      ,Source.[JobsOnHold]
      ,Source.[JobHoldAllocs]
      ,Source.[PurchOnHold]
      ,Source.[SalesOnHold]
      ,Source.[JobClassification]
      ,Source.[DateStkAdded]
      ,Source.[InspectionFlag]
    )
OUTPUT $action, Inserted.*, Deleted.*; 
ROLLBACK TRAN;

If it helps, here is the target table script.

CREATE TABLE [dbo].[dimStockCode](
	[StockCodeKey] [int] IDENTITY(1,1) NOT NULL,
	[StockCode] [varchar](30) NULL,
	[Description] [varchar](30) NULL,
	[LongDesc] [varchar](30) NULL,
	[AlternateKey1] [varchar](20) NULL,
	[AlternateKey2] [varchar](6) NULL,
	[StockUom] [varchar](3) NULL,
	[AlternateUom] [varchar](3) NULL,
	[OtherUom] [varchar](3) NULL,
	[ConvFactAltUom] [decimal](10, 6) NULL,
	[ConvMulDiv] [varchar](1) NULL,
	[ConvFactOthUom] [decimal](10, 6) NULL,
	[MulDiv] [varchar](1) NULL,
	[Decimals] [decimal](1, 0) NULL,
	[Supplier] [varchar](7) NULL,
	[CycleCount] [decimal](2, 0) NULL,
	[ProductClass] [varchar](4) NULL,
	[SerialMethod] [varchar](1) NULL,
	[KitType] [varchar](1) NULL,
	[LowLevelCode] [decimal](2, 0) NULL,
	[Buyer] [varchar](3) NULL,
	[TraceableType] [varchar](1) NULL,
	[BulkIssueFlag] [varchar](1) NULL,
	[AbcClass] [varchar](1) NULL,
	[LeadTime] [decimal](3, 0) NULL,
	[SupercessionDate] [datetime] NULL,
	[CostUom] [varchar](3) NULL,
	[PartCategory] [varchar](1) NULL,
	[WarehouseToUse] [varchar](2) NULL,
	[BuyingRule] [varchar](1) NULL,
	[Ebq] [decimal](10, 3) NULL,
	[FixTimePeriod] [decimal](2, 0) NULL,
	[PanSize] [decimal](10, 3) NULL,
	[DockToStock] [decimal](3, 0) NULL,
	[ShelfLife] [decimal](4, 0) NULL,
	[Version] [varchar](3) NULL,
	[Release] [varchar](3) NULL,
	[DemandTimeFence] [decimal](3, 0) NULL,
	[MakeToOrderFlag] [varchar](1) NULL,
	[ManufLeadTime] [decimal](3, 0) NULL,
	[GrossReqRule] [varchar](1) NULL,
	[WipCtlGlCode] [varchar](15) NULL,
	[ResourceCode] [varchar](15) NULL,
	[UserField1] [varchar](10) NULL,
	[UserField2] [decimal](17, 5) NULL,
	[UserField3] [varchar](1) NULL,
	[UserField4] [varchar](1) NULL,
	[UserField5] [varchar](1) NULL,
	[IssMultLotsFlag] [varchar](1) NULL,
	[StockOnHold] [varchar](1) NULL,
	[StockOnHoldReason] [varchar](6) NULL,
	[EccFlag] [varchar](1) NULL,
	[JobsOnHold] [varchar](1) NULL,
	[JobHoldAllocs] [varchar](1) NULL,
	[PurchOnHold] [varchar](1) NULL,
	[SalesOnHold] [varchar](1) NULL,
	[JobClassification] [varchar](4) NULL,
	[DateStkAdded] [datetime] NULL,
	[InspectionFlag] [varchar](1) NULL,
 CONSTRAINT [PK__dimStock__2CA9B2551FCDBCEB] PRIMARY KEY CLUSTERED 
(
	[StockCodeKey] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

Here's the source table script.

CREATE TABLE [dbo].[InvMaster](
	[StockCode] [char](30) NULL,
	[Description] [char](30) NULL,
	[LongDesc] [char](30) NULL,
	[AlternateKey1] [char](20) NULL,
	[AlternateKey2] [char](6) NULL,
	[StockUom] [char](3) NULL,
	[AlternateUom] [char](3) NULL,
	[OtherUom] [char](3) NULL,
	[ConvFactAltUom] [decimal](10, 6) NULL,
	[ConvMulDiv] [char](1) NULL,
	[ConvFactOthUom] [decimal](10, 6) NULL,
	[MulDiv] [char](1) NULL,
	[Decimals] [decimal](1, 0) NULL,
	[Supplier] [char](7) NULL,
	[CycleCount] [decimal](2, 0) NULL,
	[ProductClass] [char](4) NULL,
	[SerialMethod] [char](1) NULL,
	[KitType] [char](1) NULL,
	[LowLevelCode] [decimal](2, 0) NULL,
	[Buyer] [char](3) NULL,
	[TraceableType] [char](1) NULL,
	[BulkIssueFlag] [char](1) NULL,
	[AbcClass] [char](1) NULL,
	[LeadTime] [decimal](3, 0) NULL,
	[SupercessionDate] [datetime] NULL,
	[CostUom] [char](3) NULL,
	[PartCategory] [char](1) NULL,
	[WarehouseToUse] [char](2) NULL,
	[BuyingRule] [char](1) NULL,
	[Ebq] [decimal](10, 3) NULL,
	[FixTimePeriod] [decimal](2, 0) NULL,
	[PanSize] [decimal](10, 3) NULL,
	[DockToStock] [decimal](3, 0) NULL,
	[ShelfLife] [decimal](4, 0) NULL,
	[Version] [char](3) NULL,
	[Release] [char](3) NULL,
	[DemandTimeFence] [decimal](3, 0) NULL,
	[MakeToOrderFlag] [char](1) NULL,
	[ManufLeadTime] [decimal](3, 0) NULL,
	[GrossReqRule] [char](1) NULL,
	[WipCtlGlCode] [char](15) NULL,
	[ResourceCode] [char](15) NULL,
	[UserField1] [char](10) NULL,
	[UserField2] [decimal](17, 5) NULL,
	[UserField3] [char](1) NULL,
	[UserField4] [char](1) NULL,
	[UserField5] [char](1) NULL,
	[IssMultLotsFlag] [char](1) NULL,
	[StockOnHold] [char](1) NULL,
	[StockOnHoldReason] [char](6) NULL,
	[EccFlag] [char](1) NULL,
	[JobsOnHold] [char](1) NULL,
	[JobHoldAllocs] [char](1) NULL,
	[PurchOnHold] [char](1) NULL,
	[SalesOnHold] [char](1) NULL,
	[JobClassification] [char](4) NULL,
	[DateStkAdded] [datetime] NULL,
	[InspectionFlag] [char](1) NULL
)

I'm running SQL 2008 R2

Thanks for your help,

Kevin

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2012-03-23 : 11:21:46

I'm able to get a single column udpated with the following.

BEGIN TRAN;
MERGE InterConnectDW.dbo.dimStockCode AS Target
USING 
(
SELECT [StockCode]
      ,[AbcClass]
FROM SysproCompany1.dbo.InvMaster
WHERE StockCode = '##PC_A1                       '
EXCEPT
SELECT [StockCode]
      ,[AbcClass]
FROM InterConnectDW.dbo.dimStockCode
) AS Source
ON (Target.StockCode = Source.StockCode)
WHEN MATCHED 
AND 
(
	Target.[AbcClass] <> Source.[AbcClass]
)
THEN
    UPDATE SET Target.[AbcClass] = Source.[AbcClass]
OUTPUT $action, Inserted.*, Deleted.*;

Maybe there's something wrong with my approach to multiple columns?

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2012-03-23 : 13:10:04

I hate to ask the obvious, but are you running the ROLLBACK TRAN directly after the original query?

MorrisK
Yak Posting Veteran

83 Posts

Posted - 2012-03-23 : 14:02:51

Thank you for asking the obvious!

I copied an example and OBVIOUSLY did not pay attention to what it was doing!

After removing the roll back it seems to work great now.

Thanks again.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources