SQL Server How to increment variable value when Merge insert data

Question

See my below code which is inserting data into table. please tell me how could i increment @csgo variable value by one for each insert?
is it possible ? if yes then please guide me with code.

    Declare @Counter INT  
    SET @Counter=0  
    MERGE INTO tblSectionTemplate Trg          
     USING           
     (          
        SELECT TOP 100 PERCENT ID,Section FROM #TmptblSectionTemplate          
        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,  
       Order   
     )          
     VALUES           
     (          
       TRIM(@TickerID),           
       TRIM(Src.Section),           
       'A',           
       GETDATE(),  
       @Counter++	     
     )  
       
     OUTPUT inserted.SectionID  INTO #tmpAutoIDTable(ID);    

few example i found & here is link
https://stackoverflow.com/a/47336872
https://social.msdn.microsoft.com/Forums/sqlserver/en-US/e31765ba-9075-4aa8-9ea0-b45125b69fcd/auto-increment-insert-in-merge-syntax?forum=transactsql

how to use the trick in sample code from the link? thanks

Answer 1

Check if the next alternative sample contains useful things:

drop table if exists #tmpSource
drop table if exists #tmpMain
drop table if exists #tmpAutoIDTable

create table #tmpAutoIDTable(RecOrder INT)

create table #tmpSource(ID INT IDENTITY,EmpName VARCHAR(50))
insert into #tmpSource(EmpName) VALUES('Test1')
insert into #tmpSource(EmpName) VALUES('Test2')
insert into #tmpSource(EmpName) VALUES('Test3')

create table #tmpMain(ID INT ,EmpName VARCHAR(50),RecOrder INT)
-- sample existing row:
insert into #tmpMain(ID, EmpName, RecOrder) VALUES(100, 'Test2', 100) 

insert #tmpMain (ID, EmpName, RecOrder)
output inserted.RecOrder into #tmpAutoIDTable(RecOrder)
select ID, EmpName, row_number() over (order by EmpName) 
from #tmpSource s
where -- filtering source rows:
    not exists (select * from #tmpMain where EmpName = s.EmpName)


select * from #tmpMain
select * from #tmpAutoIDTable

Answer 2

You didn't follow Erland's suggestion of using the RowNumber function. For example, do
DROP TABLE IF EXISTS #tmpSource
DROP TABLE IF EXISTS #tmpMain
DROP TABLE IF EXISTS #tmpAutoIDTable

 create table #tmpAutoIDTable(RecOrder INT)
 create table #tmpSource(ID INT IDENTITY,EmpName VARCHAR(50))
 insert into #tmpSource(EmpName) VALUES('Test1')
 insert into #tmpSource(EmpName) VALUES('Test2')
 insert into #tmpSource(EmpName) VALUES('Test3')

 create table #tmpMain(ID INT ,EmpName VARCHAR(50),RecOrder INT)



 Declare @Counter INT
 SET @Counter=1
 MERGE INTO #tmpMain Trg        
 USING         
 (        
     SELECT *, Row_Number() Over(Order By EmpName) As NewRecOrder FROM #tmpSource
 ) AS Src         
 ON 1=2

 WHEN NOT MATCHED THEN         
 INSERT         
 (        
     ID,        
     EmpName,        
     RecOrder       
 )        
 VALUES         
 (        
 Src.ID,         
 TRIM(Src.EmpName),         
 Src.NewRecOrder
 )

 OUTPUT inserted.RecOrder INTO #tmpAutoIDTable(RecOrder);  
 SELECT @counter = MAX(RecOrder)+1 FROM #tmpMain

 Select ID,EmpName,RecOrder from #tmpMain

 DROP TABLE IF EXISTS #tmpSource
 DROP TABLE IF EXISTS #tmpMain
 DROP TABLE IF EXISTS #tmpAutoIDTable

Tom

Answer 3

The way you have asked the question, the answer is

SET @counter += @@rowcount

Which should be put after the MERGE statement - not inside of it, of course.

But most likely you mean something else. But what? That's not clear.

Answer 4

You have asked several questions around the same subject.

It would be much easier to help you if you would provide input data and the expected output for the entire process you are trying to accomplish.

Answer 5

please see my post again. now @csgo 's value will be inserted into table for order column. please suggest how could i increment @csgo 's value for each insert

OK, given your update, try this:

   Declare @Counter INT  
            SET @Counter=0  
            MERGE INTO tblSectionTemplate Trg          
             USING           
             (          
                SELECT ID,Section, row_number() OVER(ORDER BY YourChoiceHere) AS Counter   
   ``FROM #TmptblSectionTemplate          
             ) AS Src           
             ON UPPER(TRIM(Trg.Section)) = UPPER(TRIM(Src.Section)) AND Trg.TickerID = @TickerID          
                     
            WHEN NOT MATCHED THEN           
             INSERT           
             (          
               TickerID,          
               Section,          
               Active,          
               insertdate,  
               Order   
             )          
             VALUES           
             (          
               TRIM(@TickerID),           
               TRIM(Src.Section),           
               'A',           
               GETDATE(),  
               Counter  
             )  
                  
             OUTPUT inserted.SectionID, inserted.Counter  INTO #tmpAutoIDTable(ID, Counter);    
     
   SELECT @counter = MAX(Counter) FROM #tmpAutoIDTable