title: How to insert auto increment value in temporary table in SQL
body: <p>I have a stored procedure where I am receiving a sequence number from the user interface. The stored procedure will select records from a table and insert them into a temporary table whose structure is same as the original table, except an additional column. </p>
  
  <p>This column will hold the value "Sequence Number + 1". And for successive inserts, this value will auto increment. How should I do this? The column name where I want to keep the auto incremented value is "REV_SEQ"</p>
  
  <pre><code>  ALTER PROCEDURE [PCC_APP_OWN].[Doc_GetBlChargePatternData] 
        @RevSeq INT NULL = 0,
        @TotalCount INT OUTPUT
    AS
    BEGIN
        DECLARE @DscgPort VARCHAR(7);
        DECLARE @tempRowId INT; 
        DECLARE @tempRevSeq INT; 
  
        SET @tempRevSeq = @RevSeq + 1;
  
        DECLARE @TempMenuResult AS TABLE (REV_SEQ INT,
                                          CHRG_PTRN_DTL_ID INT,
                                          UPDT_STAMP DATETIME,
                                          UPDT_USER VARCHAR(12),
                                          ORGL_STAMP DATETIME,
                                          ORGL_USER VARCHAR(12),
                                          DEL_FLG VARCHAR(1),
                                          CHRG_PTRN_HDR_ID INT,
                                          SEQ INT,
                                          CHRG_CODE VARCHAR(3),
                                          PORT_CODE VARCHAR(7),   
                                          BASIS VARCHAR(2),
                                          UM VARCHAR(2),
                                          RATE DECIMAL(8,2),
                                          CURRENCY VARCHAR(3),
                                          PER VARCHAR(2),
                                          PREPAID_COLLECT VARCHAR(1),
                                          INV_OFC VARCHAR(6)
                                         );   
  
      SELECT @DscgPort = DSCH_PORT 
      FROM BL 
      WHERE BL_NO_UID = 1 AND CA_SEQ = 0;
  
      INSERT INTO @TempMenuResult (REV_SEQ, CHRG_PTRN_DTL_ID, UPDT_STAMP, UPDT_USER, ORGL_STAMP, ORGL_USER, DEL_FLG, CHRG_PTRN_HDR_ID, SEQ, CHRG_CODE, PORT_CODE, BASIS, UM, RATE, CURRENCY, PER, PREPAID_COLLECT, INV_OFC)
          SELECT 
              IDENTITY(INT, @RevSeq, 1) AS id, * 
          FROM 
              CHRG_PTRN_DTL CHG_1 
          WHERE 
              CHG_1.CHRG_PTRN_HDR_ID = @ChargePtrnHdrId 
              AND CHG_1.PORT_CODE = @DscgPort 
              AND CHG_1.DEL_FLG = 'N'
  
          UNION
  
          SELECT 
              IDENTITY(INT, @RevSeq, 1) AS id, * 
          FROM 
              CHRG_PTRN_DTL CHG_2 
          WHERE 
              CHG_2.PORT_CODE IS NULL 
              AND CHG_2.DEL_FLG = 'N';
  
      SELECT @TotalCount = COUNT(1) OVER () FROM @TempMenuResult;
      SELECT * FROM @TempMenuResult ORDER BY REV_SEQ;
  END
  </code></pre>