Post reply

Sequence generator using SSIS

  • October 1, 2012 at 1:03 pm

    #151101

    Hi Forum,

    I need to generate a sequence number using SSIS , this value is stored in the column as a varchar(15),

    001-00-000001

    001-00-000002

    001-00-000003

    It will NEVER reach the value 001-00-999999

    I'm trying to use a cte to generate a number

    WITH cteSequence ( SeqNo) as

    (

    SELECT 00100000001

    UNION ALL

    SELECT SeqNo + 1

    FROM cteSequence

    WHERE SeqNo < 10000000000

    )

    Is there any other way we cn acheive this task? with leading 001-00-

    thanks in advance !!

  • October 1, 2012 at 1:35 pm

    #1544126

    jampabsatish (10/1/2012)

    Hi Forum,

    I need to generate a sequence number using SSIS , this value is stored in the column as a varchar(15),

    001-00-000001

    001-00-000002

    001-00-000003

    It will NEVER reach the value 001-00-999999

    I'm trying to use a cte to generate a number

    WITH cteSequence ( SeqNo) as

    (

    SELECT 00100000001

    UNION ALL

    SELECT SeqNo + 1

    FROM cteSequence

    WHERE SeqNo < 10000000000

    )

    Is there any other way we cn acheive this task? with leading 001-00-

    thanks in advance !!

    So you want a number from 1 to 999,999. Look at: http://www.compshack.com/sql/sql-generate-sequence-number for generating the sequential number.

    The rest is just string manipulation. You want to left pad the sequential number with zeros so that it is six digits long. Something like: SELECT RIGHT('00000' + CAST(SequentialNumber AS varchar(6), 6)

    Then concatenate the '001-00-' on the front of that.

    I hope that makes sense,

    Rob

  • October 1, 2012 at 1:49 pm

    #1544132

    concatente 001-00- to 000001 (first number in the list)

    in my case 1 is different from 000001

Viewing 3 posts - 1 through 2 (of 2 total)

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