asked
Syntax Error while implementing scd-2
Hi All, may someone please help me to fix the error thrown by below sql script. T-SQL Code:- CREATE TABLE [dbo].[ACTIVE] ( [DT_FETCH] [DATETIME2] NULL, [Search_ID] [int] NOT NULL, [COMP_ID] [int] NULL, [PROD_LINK] [varchar](500) NULL, [SRPNO] [varchar](24) NULL, [OEPNO] [varchar](35) NULL, [PCNAME] [varchar](50) NULL, [PNO] [varchar](24) NULL, [PACKSIZE] [varchar](100) NULL, [SELLER_ID] [varchar](50) NULL, [BRAND_NAME] [varchar](100) NULL, [DESC] [varchar](800) NULL, [BESTPRICE_FLAG] [varchar](4) NULL, [CURR] [varchar](3) NULL, [LIST_PRICE] [decimal](14, 4) NULL, [COMP_PNO1] [varchar](35) NULL, [COMP_PNO2] [varchar](35) NULL, [COMP_PNO3] [varchar](35) NULL, [PRICE_INCVAT] [decimal](14, 4) NULL, [PRICE_EXCVAT] [decimal](14, 4) NULL, [SHIPCOST] [decimal](14, 4) NULL, [STKSTATUS] [varchar](50) NULL, [SHIPBYDAY] [varchar](50) NULL, [STKQTY] [decimal](10, 2) NULL, [SOLDQTY] [decimal](10, 2) NULL, [LastUpdated] DATETIME DEFAULT GETDATE(), [IsRowCurrent] BIT DEFAULT ('1') NULL, [ValidFrom] DATETIME DEFAULT GETDATE(), [ValidTo] DATETIME DEFAULT ('12/31/9999') ) --Create a staging table to hold new/updated customers CREATE TABLE [dbo].[StagingSupplier] ( [DT_FETCH] [DATETIME2] NULL, [Search_ID] [int] NOT NULL, [COMP_ID] [int] NULL, [PROD_LINK] [varchar](500) NULL, [SRPNO] [varchar](24) NULL, [OEPNO] [varchar](35) NULL, [PCNAME] [varchar](50) NULL, [PNO] [varchar](24) NULL, [PACKSIZE] [varchar](100) NULL, [SELLER_ID] [varchar](50) NULL, [BRAND_NAME] [varchar](100) NULL, [DESC] [varchar](800) NULL, [BESTPRICE_FLAG] [varchar](4) NULL, [CURR] [varchar](3) NULL, [LIST_PRICE] [decimal](14, 4) NULL, [COMP_PNO1] [varchar](35) NULL, [COMP_PNO2] [varchar](35) NULL, [COMP_PNO3] [varchar](35) NULL, [PRICE_INCVAT] [decimal](14, 4) NULL, [PRICE_EXCVAT] [decimal](14, 4) NULL, [SHIPCOST] [decimal](14, 4) NULL, [STKSTATUS] [varchar](50) NULL, [SHIPBYDAY] [varchar](50) NULL, [STKQTY] [decimal](10, 2) NULL, [SOLDQTY] [decimal](10, 2) NULL ) --Populate Customer table with existing customers INSERT INTO [ACTIVE] ( [DT_FETCH] , [Search_ID] , [COMP_ID] , [PROD_LINK], [SRPNO] , [OEPNO], [PCNAME], [PNO] , [PACKSIZE], [SELLER_ID], [BRAND_NAME], [DESC] , [BESTPRICE_FLAG] , [CURR] , [LIST_PRICE], [COMP_PNO1], [COMP_PNO2], [COMP_PNO3] , [PRICE_INCVAT] , [PRICE_EXCVAT], [SHIPCOST], [STKSTATUS] , [SHIPBYDAY] , [STKQTY], [SOLDQTY] ) VALUES ( '2/26/2016 3:25:57 PM', 1000001,1001, '
http://www.gsfcarparts.com/012RE0020', NULL,NULL,NULL,NULL,NULL,NULL , 'premium', 'CORE PLUG PLASTIC 37MM','N','UKL',NULL,'012RE0020',NULL,NULL, 4.9,NULL,NULL, 'in stock',NULL,NULL,NULL) ,('2/26/2016 3:26:02 PM', 1000002,1001, '
http://www.gsfcarparts.com/101ME0320', NULL,NULL,NULL,NULL,NULL,NULL , 'vetech', 'GEARBOX MOUNT', 'N', 'UKL',NULL,'101ME0320', NULL,NULL,26,NULL,NULL, 'in stock',NULL,NULL,NULL) ,( '2/26/2016 3:26:08 PM', 1000003,1001, '
http://www.gsfcarparts.com/101ME0360', NULL,NULL,NULL,NULL,NULL,NULL , 'febi', 'REAR ENGINE MOUNT', 'N', 'UKL', NULL,'101ME0360', NULL,NULL,53.76,NULL,NULL, 'in stock',NULL,NULL,NULL) ,( '2/26/2016 3:26:13 PM', 1000004,1001, '
http://www.gsfcarparts.com/101ME0380', NULL,NULL,NULL,NULL,NULL,NULL , 'metzger', 'ENGINE MOUNT', 'N', 'UKL', NULL,'101ME0380', NULL,NULL,68.5,NULL,NULL, 'Currently Unavailable',NULL,NULL,NULL) ,( '2/26/2016 3:26:18 PM', 1000005,1001, '
http://www.gsfcarparts.com/101PC0140', NULL,NULL,NULL,NULL,NULL,NULL , 'vetech', 'REAR SUBFRAME MOUNT', 'N', 'UKL', NULL,'101PC0140',NULL,NULL, 12.6,NULL,NULL, 'in stock',NULL,NULL,NULL) --Populate the staging table with the new set of customers to be imported INSERT INTO [StagingSupplier] ( [DT_FETCH] , [Search_ID] , [COMP_ID] , [PROD_LINK], [SRPNO] , [OEPNO], [PCNAME], [PNO] , [PACKSIZE], [SELLER_ID], [BRAND_NAME], [DESC] , [BESTPRICE_FLAG] , [CURR] , [LIST_PRICE], [COMP_PNO1], [COMP_PNO2], [COMP_PNO3] , [PRICE_INCVAT] , [PRICE_EXCVAT], [SHIPCOST], [STKSTATUS] , [SHIPBYDAY] , [STKQTY], [SOLDQTY] ) VALUES ( '2/26/2016 3:25:57 PM', 1000001,1001, '
http://www.gsfcarparts.com/012RE0020', NULL,NULL,NULL,NULL,NULL,NULL , 'premium', 'CORE PLUG PLASTIC 37MM','N','UKL',NULL,'012RE0020',NULL,NULL, 4.9,NULL,NULL, 'in stock',NULL,NULL,NULL) ,('4/11/2016 6:53:02 PM', 1000002,1001, '
http://www.gsfcarparts.com/101ME0320', NULL,NULL,NULL,NULL,NULL,NULL , 'vetech', 'GEARBOX MOUNT', 'N', 'UKL',NULL,'101ME0320', NULL,NULL,26,NULL,NULL, 'in stock',NULL,NULL,NULL) ,( '2/26/2016 3:26:08 PM', 1000022,1005, '
http://www.gsfcarparts.com/101ME0360', NULL,NULL,NULL,NULL,NULL,NULL , 'febi', 'REAR ENGINE MOUNT', 'N', 'UKL', NULL,'101ME0360', NULL,NULL,53.76,NULL,NULL, 'in stock',NULL,NULL,NULL) ,( '2/26/2016 3:26:13 PM', 1000004,1001, '
http://www.CHECKHERE.com/101ME0380', NULL,NULL,NULL,NULL,NULL,NULL , 'metzger', 'ENGINE MOUNT', 'N', 'UKL', NULL,'101ME0380', NULL,NULL,68.5,NULL,NULL, 'Currently Unavailable',NULL,NULL,NULL) ,( '2/26/2016 3:26:18 PM', 1000005,1001, '
http://www.CHECKHERE2.com/101PC0140', NULL,NULL,NULL,NULL,NULL,NULL , 'vetech', 'REAR SUBFRAME MOUNT', 'N', 'UKL', NULL,'101PC0140',NULL,NULL, 12.6,NULL,NULL, 'in stock',NULL,NULL,NULL) ,( '2/26/2016 3:26:08 PM', 1000055,1055, '
http://www.gsfcarparts.com/101ME0360', NULL,NULL,NULL,NULL,NULL,NULL , 'febi', 'REAR ENGINE MOUNT', 'N', 'UKL', NULL,'101ME0360', NULL,NULL,53.76,NULL,NULL, 'in stock',NULL,NULL,NULL) -- -- Mixed Merge - Type 1 & 2 INSERT INTO ACTIVE ( [DT_FETCH] , [Search_ID] , [COMP_ID] , [PROD_LINK], [SRPNO] , [OEPNO], [PCNAME], [PNO] , [PACKSIZE], [SELLER_ID], [BRAND_NAME], [DESC] , [BESTPRICE_FLAG] , [CURR] , [LIST_PRICE], [COMP_PNO1], [COMP_PNO2], [COMP_PNO3] , [PRICE_INCVAT] , [PRICE_EXCVAT], [SHIPCOST], [STKSTATUS] , [SHIPBYDAY] , [STKQTY], [SOLDQTY] ,GETDATE() --LastUpdated ,'1' --IsRowCurrent ,GETDATE () --ValidFrom ,'9999-12-31' --ValidTo FROM ( MERGE ACTIVE AS [Target] USING [StagingSupplier] AS [Source] ON Target.Search_ID = Source.Search_ID AND Target.COMP_ID = Source.COMP_ID AND Target.COMP_PNO1 = Source.COMP_PNO1 AND Target.IsRowCurrent = 1 WHEN MATCHED AND ( Target.DT_FETCH <> Source.DT_FETCH OR Target.PROD_LINK <> Source.PROD_LINK OR Target.SRPNO <> Source.SRPNO OR Target.OEPNO <> Source.OEPNO OR Target.PCNAME <> Source.PCNAME OR Target.PNO <> Source.PNO OR Target.PACKSIZE <> Source.PACKSIZE OR Target.SELLER_ID <> Source.SELLER_ID OR Target.BRAND_NAME <> Source.BRAND_NAME OR Target.DESC <> Source.DESC OR Target.BESTPRICE_FLAG <> Source.BESTPRICE_FLAG OR Target.CURR <> Source.CURR OR Target.LIST_PRICE <> Source.LIST_PRICE OR Target.COMP_PNO2 <> Source.COMP_PNO2 OR Target.COMP_PNO3 <> Source.COMP_PNO3 OR Target.PRICE_INCVAT <> Source.PRICE_INCVAT OR Target.PRICE_EXCVAT <> Source.PRICE_EXCVAT OR Target.SHIPCOST <> Source.SHIPCOST OR Target.STKSTATUS <> Source.STKSTATUS OR Target.SHIPBYDAY <> Source.SHIPBYDAY OR Target.STKQTY <> Source.STKQTY OR Target.SOLDQTY <> Source.SOLDQTY OR Target.LastUpdated <> Source.LastUpdated OR Target.IsRowCurrent <> Source.IsRowCurrent OR Target.ValidFrom <> Source.ValidFrom OR Target.ValidTo <> Source.ValidTo ) THEN UPDATE SET IsRowCurrent = 0 ,LastUpdated = GETDATE() ,ValidTo = GETDATE() WHEN NOT MATCHED BY TARGET THEN INSERT ( DT_FETCH ,PROD_LINK ,SRPNO ,OEPNO ,PCNAME ,PNO ,PACKSIZE ,SELLER_ID ,BRAND_NAME ,DESC ,BESTPRICE_FLAG ,CURR ,LIST_PRICE ,COMP_PNO2 ,COMP_PNO3 ,PRICE_INCVAT ,PRICE_EXCVAT ,SHIPCOST ,STKSTATUS ,SHIPBYDAY ,STKQTY ,SOLDQTY ,LastUpdated ,IsRowCurrent ,ValidFrom ,ValidTo ) VALUES ( SOURCE.PROD_LINK ,SOURCE.SRPNO ,SOURCE.OEPNO ,SOURCE.PCNAME ,SOURCE.PNO ,SOURCE.PACKSIZE ,SOURCE.SELLER_ID ,SOURCE.BRAND_NAME ,SOURCE.DESC ,SOURCE.BESTPRICE_FLAG ,SOURCE.CURR ,SOURCE.LIST_PRICE ,SOURCE.COMP_PNO2 ,SOURCE.COMP_PNO3 ,SOURCE.PRICE_INCVAT ,SOURCE.PRICE_EXCVAT ,SOURCE.SHIPCOST ,SOURCE.STKSTATUS ,SOURCE.SHIPBYDAY ,SOURCE.STKQTY ,SOURCE.SOLDQTY ,GETDATE() --LastUpdated ,1 --IsRowCurrent ,GETDATE() --ValidFrom ,'9999-12-31' --ValidTo ) WHEN NOT MATCHED BY SOURCE AND Target.IsRowCurrent = 1 THEN UPDATE SET IsRowCurrent = 0 ,LastUpdated = GETDATE() ,ValidTo = GETDATE() OUTPUT $action AS Action ,[Source].* ) AS MergeOutput WHERE MergeOutput.Action = 'UPDATE' AND Email IS NOT NULL ; The error i am getting is: Msg 102, Level 15, State 1, Line 29 Incorrect syntax near '('. Msg 156, Level 15, State 1, Line 51 Incorrect syntax near the keyword 'DESC'. Thanks
Hi there sqlthirts There seem to be multiple errors in there I'm afraid, did you develop this yourself or is it's someone else's code or perhaps and assignment? The way to debug it is to take a look at each statement in turn and work out where the problem section is, you'll find that most of it runs but one section wont. Once you find that section then you can have a look there to see what the problems are, if it's a complex section then look to simplify it i.e. work on sub queries rather than the entire query to see what you can spot (or what the error is in the messages windows, it will tell you what's wrong). Feel free to ask any other questions or share your findings along the way and I'll work with you to achieve the correct outcome. Hope that helps, here all afternoon :) Mart P.S. some hints: Brackets () need to be in pairs. DESC is a reserved word. Review INSERT statements noting how they are written (hint, look at the table default values too) Columns in two tables can only be used for comparison if both tables contain those columns.
Hi Mart, i refer the link "
http://www.purplefrogsystems.com/blog/2012/01/using-t-sql-merge-to-load-data-warehouse-dimensions/ ", to implement my scenario. i will look into it, and if any finding i will share it.
That's a lot of code to digest. It took me a couple of minutes just to get rid of the line numbers! I reckon you've got a bit buried in the details here. How about getting a cut-down example to work first? Just the minimum 2 or 3 columns in each table. Either that will still fail, in which case you can post the simpler version here and people might be able to help more easily. Or it might work, in which case you can add the other columns back in. Don't add them all at once. Just do 2 or 3 at a time and test each iteration.
19 People are following this question.
