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

  • Try SEQUENCE if you are using SQL Server 2012 version.


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, November 05, 2013 10:02 AM
  • You can use SQL Server 2012 SEQUENCE object which has CYCLE setting:

    http://technet.microsoft.com/en-us/library/ff878091.aspx

    For prior versions build a scalar UDF:

    http://www.sqlusa.com/bestpractices/training/scripts/userdefinedfunction/


    Kalman Toth Database & OLAP Architect IPAD SELECT Query Video Tutorial 3.5 Hours
    New Book / Kindle: Exam 70-461 Bootcamp: Querying Microsoft SQL Server 2012


    Tuesday, November 05, 2013 10:02 AM
    Moderator
  • Thanks. But I am using SQL Server 2008. Is there any way to do in 2008
    Tuesday, November 05, 2013 10:02 AM
  • A similar thread giving multiple ways to achieve it,

    http://social.msdn.microsoft.com/Forums/en-US/0294085f-724a-4be1-b195-bd7a3d36b177/how-to-insert-a-column-with-repeated-vales?forum=transactsql


    Please use Marked as Answer if my post solved your problem and use Vote As Helpful if a post was useful.

    Tuesday, November 05, 2013 10:04 AM
  • Thanks Kalman. Is there any option in SQL Server 2008. 

    My insert statement is mentioned below.

    insert into @ProductionResponseProduction
          SELECT
          
          rd.RowName,
          rd.UomName,
          ptemp.Value,
          ptemp.line,
          ptemp.TargetAndRangeStatus
                
          from @PRTemp ptemp 
          LEFT JOIN @ReportDefintion rd ON ptemp.ID =rd.ProductionResponseID
          ORDER BY rd.RowName

    Tuesday, November 05, 2013 10:05 AM
  • 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
  • pepcoder,

    Possible to have a computed column ??

    I mean, we can have a regular identity column as id and then have a computed column that gives numbers 1-6 in a cyclic fashion using the mod operator..

    Somethin like:

    select id,name,case when id%6=0 then 6 else id%6 end 
    as keycol_name
    from #temp


    Thanks,
    JK

    <Please mark as 'answered' if this answers your query> <Please mark as 'helpful' if this was helpful to your query>

    Tuesday, November 05, 2013 11:00 AM
  • Thank you very much. Your suggestion worked. :-) Sorry for the late reply

    Cheers

    -pep

    Monday, November 11, 2013 10:37 AM
  • create table test (ParameterName varchar(10),linedetail int)
    insert into test values('AAA',1),('BBB',2),('CCC',3)
    Select rownum,ParameterName,linedetail from test 
    Cross Apply (values(1),(2),(3),(4),(5),(6)) d (rownum)
    drop table test

    Monday, November 11, 2013 3:33 PM
    Moderator