SQL Server NULL Value inserted when insert done by Merge Statement

Question

I have table where i am inserting data using merge statement but NULL is getting inserted into OrderID field.

First see my full SP Code

--<?xml version="1.0" encoding="utf-16"?>                                                
--<Root>                                                
--  <PeriodicalData>                                                
--    <Section>Consensus Model</Section>                                                
--    <LineItem>Net Sales</LineItem>                                                
--    <XFundCode>TRIN</XFundCode>                                                
--    <StandardDate>2010 FY</StandardDate>                                                
--    <StandardValue>65225</StandardValue>                                                
--  </PeriodicalData>                                                
--  <PeriodicalData>                                                
--    <Section>Consensus Model</Section>                                                
--    <LineItem>Net Sales</LineItem>                                                
--    <XFundCode>TRIN</XFundCode>                                                
--    <StandardDate>2011 FY</StandardDate>                                                
--    <StandardValue>108249</StandardValue>                                                
--  </PeriodicalData>                                                
--</Root>                                                  

--<?xml version="1.0" encoding="utf-16"?>                                    
--<Root>                                    
--  <TenQKLiConfig>                                    
--    <Ticker>TDY1</Ticker>                                    
--    <Section>Consensus Model</Section>                                    
--    <LI>Cost of Revenue</LI>                                    
--    <StandrdFormula />                                    
--    <StandrdFormulaActual />                                    
--    <AllowedDecimalPlace>1</AllowedDecimalPlace>                                    
--    <CurrencySign>$</CurrencySign>                                    
--    <CurrencyCode>en-US</CurrencyCode>                                    
--    <AllowPercentageSign>false</AllowPercentageSign>                                    
--    <AllowComma>true</AllowComma>                                    
--    <QCCheck>false</QCCheck>                                    
--    <QCType>SUMQ</QCType>                                    
--    <BlueMatrix1stElementFormula>"Consensus Model~Cost of Revenue~3330"/"Consensus Model~Net Revenue~5833"</BlueMatrix1stElementFormula>                                    
--    <DevelopmentStage />                                    
--    <CrossCalc1Q />                                    
--    <CrossCalc2Q />                                    
--    <CrossCalc3Q />                                    
--    <CrossCalc4Q />                                    
--    <CrossCalcFY />                                    
--    <GH_FontStyle>Regular</GH_FontStyle>                                    
--    <GH_Strikeout>false</GH_Strikeout>                                    
--    <FontStyle>Regular</FontStyle>                                    
--    <Strikeout>false</Strikeout>                                    
--  </TenQKLiConfig>                                          
--  </Root>                                    

ALTER PROCEDURE [dbo].[USP_ImportBogeyDataFromXML]                                                          
 @TickerID NVARCHAR(20),                                                         
 @UserID NVARCHAR(20),                                                        
 @BogyXML XML,                                        
 @LiConfigXML XML,                                    
 @STATUS VARCHAR(200) OUTPUT                                                           
AS                                                 

BEGIN          
 DECLARE @MasterID INT, @SectionLiOrder INT
 SET @MasterID=0                                                
 SET @SectionLiOrder = 1
 BEGIN TRY                                       
 SET QUOTED_IDENTIFIER OFF;                                  
 BEGIN TRAN    

 BEGIN --Delete all ticker specific data first from bogey tables                       
 DELETE c                        
 FROM TblLineItemTemplate a INNER JOIN                        
 tblTicker_Bogey c ON a.ID = c.LineItemID INNER JOIN                        
 tblSectionTemplate b ON c.SectionID = b.SectionID                        
 WHERE a.TickerID=@TickerID AND b.TickerID=@TickerID                        

 DELETE FROM tblTicker_LiConfig WHERE TickerID=@TickerID                  

 DELETE c                        
 FROM  TblLineItemTemplate a INNER JOIN                        
 tblSectionLineItemTemplate c ON a.ID = c.LineItemID INNER JOIN                        
 tblSectionTemplate b ON c.SectionID = b.SectionID                        
 WHERE a.TickerID=@TickerID AND b.TickerID=@TickerID                        

 DELETE d                        
 FROM tblCalenderDetail d INNER JOIN tblCalenderMaster M                        
 ON D.MasterID=M.ID                        
 WHERE M.TickerID=@TickerID                        

 DELETE FROM tblCalenderMaster WHERE TickerID=@TickerID                        
 END    

 BEGIN--Inserting Data into tblCalenderMaster table
 IF NOT EXISTS(SELECT * FROM tblCalenderMaster WHERE TickerID=@TickerID)                                                      
 BEGIN                                                      
 INSERT INTO tblCalenderMaster(TickerID,Following53Weeks,TransitionPeriod)                                                              
 VALUES(@TickerID,'N','N')                                                           

 SELECT @MasterID = SCOPE_IDENTITY()                                              
 END                                                      
 ELSE                                                      
 BEGIN                                                      
 SELECT @MasterID = ID FROM tblCalenderMaster WHERE TickerID=@TickerID                                                
 END    
 END

 BEGIN --Dump 10QK xml data into #TmpTenQKData temporary table
 CREATE TABLE #TmpTenQKData 
 (
     ID INT Identity,
 Section NVARCHAR(MAX), 
 LineItem NVARCHAR(MAX),
 XFundCode NVARCHAR(MAX),
 StandardDate NVARCHAR(MAX),
 StandardValue VARCHAR(MAX),
 ActualProvidedByCompany VARCHAR(MAX)
 )

 INSERT INTO #TmpTenQKData 
 SELECT REPLACE(col.value('(Section/text())[1]', 'NVARCHAR(MAX)'),'''','''''') AS Section
 ,REPLACE(col.value('(LineItem/text())[1]', 'NVARCHAR(MAX)'),'''','''''')  AS LineItem   
 ,REPLACE(col.value('(XFundCode/text())[1]', 'NVARCHAR(MAX)'),'''','''''') AS XFundCode
 ,col.value('(StandardDate/text())[1]', 'NVARCHAR(MAX)') AS StandardDate
 ,col.value('(StandardValue/text())[1]', 'VARCHAR(MAX)') AS StandardValue
 ,col.value('(ActualProvidedByCompany/text())[1]', 'VARCHAR(MAX)') AS ActualProvidedByCompany
 FROM @BogyXML.nodes('/Root/PeriodicalData') AS tab (col) 
 END


 BEGIN --INSERTNG/UPDATING Unique LineItem into TblLineItemTemplate
 MERGE INTO TblLineItemTemplate Trg
 USING 
 (
 SELECT TOP 100 PERCENT MAX(ID) AS ID,
 MAX(REPLACE(LineItem,'''','''''')) AS LineItem,
 MAX(REPLACE(XFundCode,'''','''''')) AS XFundCode
 /*MAX(TickerID) AS TickerID*/
 FROM #TmpTenQKData
 GROUP BY LineItem
 ORDER BY ID
 ) AS Src 
 ON UPPER(TRIM(Trg.LineItem)) = UPPER(TRIM(Src.LineItem)) AND Trg.TickerID = @TickerID /*Src.TickerID*/

 WHEN MATCHED THEN 
 UPDATE SET 
 XFundCode = Src.XFundCode,
 Action = 'U',
 Insertdate = GETDATE()
 WHEN NOT MATCHED THEN 
 INSERT 
 (
 TickerID,
 LineItem,
 XFundCode,
 Action,
 UserID,
 Insertdate
 )
 VALUES 
 (
 TRIM(@TickerID), 
 TRIM(Src.LineItem), 
 TRIM(Src.XFundCode), 
 'I', @UserID, 
 GETDATE()
 );
 END


 BEGIN --INSERTNG/UPDATING Unique Section into tblSectionTemplate
 MERGE INTO tblSectionTemplate Trg
 USING 
 (
 SELECT TOP 100 PERCENT MAX(ID) AS ID,
 MAX(REPLACE(Section,'''','''''')) AS Section
 FROM #TmpTenQKData
 GROUP BY Section
 ORDER BY ID
 ) AS Src 
 ON UPPER(TRIM(Trg.Section)) = UPPER(TRIM(Src.Section)) AND Trg.TickerID = @TickerID

 WHEN NOT MATCHED THEN 
 INSERT 
 (
 TickerID,
 Section,
 Active,
 insertdate,
 OrderID
 )
 VALUES 
 (
 TRIM(@TickerID), 
 TRIM(Src.Section), 
 'A', 
 GETDATE(), 
 (SELECT MAX(ISNULL(OrderID,0)+1) FROM tblSectionTemplate WHERE TickerID=@TickerID)
 );
 END




 BEGIN --INSERTNG/UPDATING Unique Section into tblSectionLineItemTemplate
 MERGE INTO tblSectionLineItemTemplate Trg
 USING 
 (
 --SELECT DISTINCT s.SectionID AS SectionID, l.ID AS LineItemID
 --FROM #TmpTenQKData a JOIN TblLineItemTemplate l ON a.LineItem = l.LineItem 
 --JOIN tblSectionTemplate s ON a.Section = s.Section

 SELECT TOP 100 PERCENT s.SectionID AS SectionID, l.ID AS LineItemID
 FROM (SELECT DISTINCT Section,LineItem FROM #TmpTenQKData) a RIGHT OUTER JOIN TblLineItemTemplate l ON a.LineItem = l.LineItem 
 RIGHT OUTER JOIN tblSectionTemplate s ON a.Section = s.Section
 WHERE s.TickerID=@TickerID AND l.TickerID=@TickerID 
 ORDER BY s.OrderID,l.ID

 ) AS Src 
 ON ISNULL(Trg.SectionID,0) = Src.SectionID AND ISNULL(Trg.LineItemID,0) = Src.LineItemID

 WHEN NOT MATCHED THEN 
 INSERT 
 (
 SectionID,
 LineItemID,
 Active,
 InsertDate,
 UserID,
 OrderID
 )
 VALUES 
 (
 Src.SectionID, 
 Src.LineItemID, 
 'A', 
 GETDATE(), 
 @UserID,
 (SELECT MAX(ISNULL(OrderID,0)+1) FROM tblSectionLineItemTemplate WHERE SectionID=ISNULL(Trg.SectionID,0) AND LineItemID=ISNULL(Trg.LineItemID,0))
 );

 END

 select * from tblSectionLineItemTemplate

 /*
 BEGIN --UPDATE LineItem ID in tblCSM_ModelDetails table if CSM created for selected Ticker
 UPDATE c 
 SET LineItemID=l.ID
 FROM tblCSM_ModelDetails c JOIN TblLineItemTemplate l
 ON UPPER(TRIM(c.DisplayInCSM))=UPPER(TRIM(l.LineItem))
 WHERE c.CSM_ID in (select CSM_ID from tblCSM_Tuner_Client where tickerid=@TickerID) AND c.TYpe='LINEITEM' AND
 l.TickerID=@TickerID
 END 
 */

    SET @STATUS='SUCCESS'       

 IF OBJECT_ID('TempDB..#TmpTenQKData') IS NOT NULL  
 BEGIN  
 drop table #TmpTenQKData  
 END  

 COMMIT TRAN
 END TRY
 BEGIN CATCH                                                          
 --PRINT 'err'                                                       
 ROLLBACK TRAN                                                          

 DECLARE @ErrorMessage NVARCHAR(4000);                               
 DECLARE @ErrorSeverity INT;                                                            
 DECLARE @ErrorState INT;                                                            

 SELECT @ErrorMessage = ERROR_MESSAGE() + ' occurred at Line_Number: ' + CAST(ERROR_LINE() AS VARCHAR(50)),                                   
 @ErrorSeverity = ERROR_SEVERITY(),                                                            
 @ErrorState = ERROR_STATE();                                           

 SET @STATUS='FAIL'                                                        
 PRINT 'Err---> '+  @ErrorMessage                                                   
 RAISERROR                                     
 (                                    
 @ErrorMessage, -- Message text.                                                            
 @ErrorSeverity, -- Severity.                                                            
 @ErrorState -- State.                                                            
 );                                         
 END CATCH     
END 

Now pointing the area where problem occurs.

 MERGE INTO tblSectionLineItemTemplate Trg
 USING 
 (
 --SELECT DISTINCT s.SectionID AS SectionID, l.ID AS LineItemID
 --FROM #TmpTenQKData a JOIN TblLineItemTemplate l ON a.LineItem = l.LineItem 
 --JOIN tblSectionTemplate s ON a.Section = s.Section

 SELECT TOP 100 PERCENT s.SectionID AS SectionID, l.ID AS LineItemID
 FROM (SELECT DISTINCT Section,LineItem FROM #TmpTenQKData) a RIGHT OUTER JOIN TblLineItemTemplate l ON a.LineItem = l.LineItem 
 RIGHT OUTER JOIN tblSectionTemplate s ON a.Section = s.Section
 WHERE s.TickerID=@TickerID AND l.TickerID=@TickerID 
 ORDER BY s.OrderID,l.ID

 ) AS Src 
 ON ISNULL(Trg.SectionID,0) = Src.SectionID AND ISNULL(Trg.LineItemID,0) = Src.LineItemID

 WHEN NOT MATCHED THEN 
 INSERT 
 (
 SectionID,
 LineItemID,
 Active,
 InsertDate,
 UserID,
 OrderID
 )
 VALUES 
 (
 Src.SectionID, 
 Src.LineItemID, 
 'A', 
 GETDATE(), 
 @UserID,
 (SELECT MAX(ISNULL(OrderID,0)+1) FROM tblSectionLineItemTemplate WHERE SectionID=ISNULL(Trg.SectionID,0) AND LineItemID=ISNULL(Trg.LineItemID,0))
 );

(SELECT MAX(ISNULL(OrderID,0)+1) FROM tblSectionLineItemTemplate WHERE SectionID=ISNULL(Trg.SectionID,0) AND LineItemID=ISNULL(Trg.LineItemID,0))

The above way i try to fetch data and add by 1 but after merge statement finish i saw NULL value is there in OrderID field.
i do not want to make my OrderID field identity type.

I try to place a variable in insert like @OrderID++ but when i mention variable with ++ then SP throw error at compilation time.

Please tell me where i made the mistake for which NULL is getting inserted into OrderID field.

Thanks

WHEN NOT MATCHED THEN 
INSERT 
(
 SectionID,
 LineItemID,
 Active,
 InsertDate,
 UserID,
 OrderID
)
VALUES 
(
 Src.SectionID, 
 Src.LineItemID, 
 'A', 
 GETDATE(), 
 @UserID,
 @OrderID++
);

Before insert any value into OrderID field, i want to fetch last value of OrderID like this way WHERE SectionID=ISNULL(Trg.SectionID,0) AND LineItemID=ISNULL(Trg.LineItemID,0)) if value found then add +1 and insert new incremented OrderID.

please guide me how to achieve this with Merge statement when inserting data. thanks

Answer 1

There is more than one thing that looks wrong here. For the actual question question you ask, the problem may be here:

MAX(ISNULL(OrderID,0)+1)

You probably want ISNULL around MAX rather than the other way round. That is, if there is no matching row in tblSectionLineItemTemplate, MAX will return NULL, and with ISNULL you can get back 1 for the subquery.

But once you have fixed that, I think you will find that all inserted rows get the same order id, which I don't think is what you want.

Then I looked at your USING clause and I see this:

FROM (SELECT DISTINCT Section,LineItem FROM #TmpTenQKData) a 
RIGHT OUTER JOIN TblLineItemTemplate l ON a.LineItem = l.LineItem 
RIGHT OUTER JOIN tblSectionTemplate s ON a.Section = s.Section

Let me say it from the start: RIGHT JOIN gives me a headache, so I rather not try to understand what this is supposed to do. However, joining to the same temp table twice on different keys is.... well, bewildering. I don't know what you are up to, but I don't think this is right.