geerate businees sequece number

  • Create table #temp(

    ID int

    ,name varchar(20)

    ,buss_seqno varchar(100) NULL

    )

    GO

    INSERT INTO #temp

    SELECT 1,'John',NULL

    UNION

    SELECT 2,'Bayer',NULL

    UNION

    SELECT 3,'Chris' ,NULL

    select * from #temp

    -- I Have a constant for sequence no

    DECLARE @start_seqno int

    ,@rel_seq int

    ,@rem_val int

    --Now for buss_seqno column in # temp table i havve to updated with below logic

    SET @start_seqno='12789'

    set @rem_val = (@start_seqno % 7)

    set @rel_seq=@start_seqno*10+@rem_val

    set @start_seqno=@start_seqno+1

    --so for each row of temp table i have to update the

    UPDATE #temp SET buss_seqno=@rel_seq where ID = @ID

    -- In the above update we haeHow to update the #temp table without using while or cursors

  • first of all your DDL script is not complete, you reference a column in #temp called tax_ref_id which you have not defined and a variable called @tax_ref_id which again you have not defined

    what is your expected result, John is 12789, Bayer is 12790 and Chris is 12791

  • Sory for the confudion i have updated the query there is no @tax_ref_id

    For each roe of #temp table i have to updated the buss_seqnno

  • is there a particular reason you cant use while loops or cursors?

  • Something like this?

    DECLARE @start_seqno INT

    SET @start_seqno='12789';

    WITH t1(N) AS (SELECT 1 UNION ALL SELECT 1),

    t2(N) AS (SELECT 1 FROM t1 x, t1 y),

    t3(N) AS (SELECT 1 FROM t2 x, t2 y),

    t4(N) AS (SELECT 1 FROM t3 x, t3 y),

    Tally(N) AS (SELECT 0 UNION ALL

    SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL))

    FROM t4 x, t4 y)

    UPDATE #temp SET buss_seqno=new.buss_seqno

    FROM (SELECT ID, name, new.buss_seqno

    FROM (SELECT ID, name, buss_seqno, ROW_NUMBER() OVER (ORDER BY ID) AS rn

    FROM #temp) old

    INNER JOIN (SELECT start_seqo*10+rem_val AS buss_seqno, N+1 AS N

    FROM Tally

    CROSS APPLY (SELECT N+@start_seqno AS start_seqo) b

    CROSS APPLY (SELECT start_seqo % 7 AS rem_val) c) new ON old.rn = new.N) new

    WHERE #temp.ID = new.ID


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

  • Better way: -

    UPDATE #temp SET buss_seqno=new.buss_seqno

    FROM (SELECT ID, name, start_seqo*10+rem_val AS buss_seqno

    FROM (SELECT ID, name, buss_seqno, ROW_NUMBER() OVER (ORDER BY ID) - 1 AS rn

    FROM #temp) a

    CROSS APPLY (SELECT rn+@start_seqno AS start_seqo) b

    CROSS APPLY (SELECT start_seqo % 7 AS rem_val) c) new

    WHERE #temp.ID = new.ID


    Forever trying to learn
    My blog - http://www.cadavre.co.uk/
    For better, quicker answers on T-SQL questions, click on the following...http://www.sqlservercentral.com/articles/Best+Practices/61537/
    For better, quicker answers on SQL Server performance related questions, click on the following...http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply