Generating a counter table in SQL

  • Hi All,

    I am currently looking at a project that requires acounter table - I have done this very easilly in Excel with a VBScript - but want to do it with a SQL Script.

    Any thoughts ?

    Sub updatevalues()

    Dim Total

    Dim Counter

    Dim row

    Dim RecordNumber

    RecordNumber = 1

    Total = 1

    Counter = 1

    row = 2

    For Total = 1 To 300

    For Counter = 1 To Total

    Sheets("Sheet1").Cells(row, 1).Value = RecordNumber

    Sheets("Sheet1").Cells(row, 2).Value = Total

    Sheets("Sheet1").Cells(row, 3).Value = Counter

    row = row + 1

    RecordNumber = RecordNumber + 1

    Next Counter

    Next Total

    End Sub

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

  • Hi

    You can do it similarly with t-sql:

    declare @RecNum int=1

    declare @Total int=1

    declare @Cnt int=1

    declare @row int=1

    if object_id('tempdb..#Sheet') is not null

    drop table #Sheet

    create table #Sheet(RecordNumber int,Total int,[Counter] int)

    while @Total<=300

    begin

    while @Cnt<=@Total

    begin

    insert into #Sheet(RecordNumber,Total,[Counter])

    values(@RecNum,@Total,@Cnt)

    set @RecNum +=1

    set @Cnt +=1

    end

    set @Cnt =1

    set @Total +=1

    end

    select * from #Sheet

    drop table #Sheet

    Igor Micev,My blog: www.igormicev.com

  • SteveEClarke (4/30/2013)


    Hi All,

    I am currently looking at a project that requires acounter table - I have done this very easilly in Excel with a VBScript - but want to do it with a SQL Script.

    Any thoughts ?

    The best (and best-known) way to do this is with a tally table, as John M points out. How do you intend to use the generated sequence? Tally tables can be generated on-the-fly depending upon usage.

    “Write the query the simplest way. If through testing it becomes clear that the performance is inadequate, consider alternative query forms.” - Gail Shaw

    For fast, accurate and documented assistance in answering your questions, please read this article.
    Understanding and using APPLY, (I) and (II) Paul White
    Hidden RBAR: Triangular Joins / The "Numbers" or "Tally" Table: What it is and how it replaces a loop Jeff Moden

  • Brilliant - thanks for the advice.

    It is used for Crystal Reports printing - whereby it links the qty to print on the total - so returns the numbers of sheets/labels to print.

    It is a one off creation of the table - but was looking at the SQL equivilant rather than Excel, Access -> SQL update.

    Thanks

    Steve

    ________________________________________________________________________________________________
    Regards
    Steve
    SQL 2008 DBA/DBD - MCTS/MCITP

    Please don't trust me, test the solutions I give you before using them.

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

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