Generating a counter table in SQL

Question

Post reply

Generating a counter table in SQL

SteveEClarke

SSCertifiable

Points: 5161
More actions
April 30, 2013 at 4:54 am

#272004

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.

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

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

Igor Micev SSC-Dedicated Points: 33109 More actions · Answer 1

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

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 2

http://www.sqlservercentral.com/articles/T-SQL/62867/

John

ChrisM@Work SSC Guru Points: 186127 More actions · Answer 3

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

SteveEClarke SSCertifiable Points: 5161 More actions · Answer 4

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.