none
Increment a unique ID only up to 6 while inserting data in to a temp table

    Question

  • Hi,

    Can anyone help me out with this requirement. I want to insert value into a temp table with an autoincrement ID as the first column. When the ID incremented up to 6 i should be again reset to 1 and insert another six set of rows .

    A sample format is mentioned below which I have manually inserted in a temp table.

    Please help me .Very urgent requirement.

    Tuesday, November 05, 2013 9:53 AM

Answers

  • Hi Pepcoder,

    Refer the below technique. I am using sys.objects as a table to simulate the problem. You can change the sys.object subquery to your appropriate table.

    -------------------------------------------------------------------------

    ;WITH CTE AS (
    SELECT [NAME],OBJECT_ID,ROW_NUMBER() OVER (ORDER BY TYPE_DESC) RN
    FROM (SELECT * FROM SYS.OBJECTS) X
    )
    SELECT [NAME],OBJECT_ID, CASE
    WHEN RN % 6 = 0 THEN 6
    ELSE RN % 6 END AS ID
    FROM CTE


    Regards, RSingh



    • Proposed as answer by HimanshuSharma Tuesday, November 05, 2013 11:59 AM
    • Edited by RSingh() Tuesday, November 05, 2013 1:51 PM
    • Marked as answer by pepcoder Monday, November 11, 2013 10:35 AM
    Tuesday, November 05, 2013 10:10 AM

All replies