Autoincrement variable

Question

Post reply

Autoincrement variable

dana_turcanu1981

SSChasing Mays

Points: 641
More actions
December 1, 2008 at 3:27 am

#194518

Hy.
I need a temporary table that loks like this:
Col1
1
2
3
4
5
Can I create it with a select statement? I wouldn't like to use a "for"
Thanks

Viewing 13 posts - 1 through 12 (of 12 total)

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

ChiragNS One Orange Chip Points: 26137 More actions · Answer 1

Insert into #table (col1)

select TOP 100 ROW_NUMBER ( ) OVER (order by sys.columns.object_id) as 'rownum'

from sys.columns ,sys.tables

"Keep Trying"

Madhivanan-208264 SSCertifiable Points: 7516 More actions · Answer 2

dana_turcanu1981 (12/1/2008)
Hy.
I need a temporary table that loks like this:
Col1
1
2
3
4
5
Can I create it with a select statement? I wouldn't like to use a "for"
Thanks

Do you want to create a number table?

Madhivanan

Failing to plan is Planning to fail

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

dana_turcanu1981 (12/1/2008)
Hy.
I need a temporary table that loks like this:
Col1
1
2
3
4
5
Can I create it with a select statement? I wouldn't like to use a "for"
Thanks

This is quite quick:

SELECT IDENTITY (INT, 1, 1) AS RowID, ...

INTO #Temp

FROM #SourceTable

Alternatively, create a numbers / tally table (you know you want to): http://www.sqlservercentral.com/articles/TSQL/62867/

Cheers

ChrisM

^{“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

dana_turcanu1981 SSChasing Mays Points: 641 More actions · Answer 4

Chirag (12/1/2008)
Insert into #table (col1)
select TOP 100 ROW_NUMBER ( ) OVER (order by sys.columns.object_id) as 'rownum'
from sys.columns ,sys.tables

The result is that I want, but the solution "from sys.columns ,sys.tables"... very interesting 😀

ChiragNS One Orange Chip Points: 26137 More actions · Answer 5

i think i got this from some code written by Jeff Modem 🙂

"Keep Trying"

Dugi SSCoach Points: 17998 More actions · Answer 6

Chirag (12/2/2008)
i think i got this from some code written by Jeff Modem 🙂

Yes he has skills to generate the code in professional way, simply he's Code Generator!

:hehe:

============================================================
SELECT YOUR PROBLEM FROM SSC.com WHERE PROBLEM DESCRIPTION =
http://www.sqlservercentral.com/articles/Best+Practices/61537/[/url]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 7

dana_turcanu1981 (12/1/2008)
Hy.
I need a temporary table that loks like this:
Col1
1
2
3
4
5
Can I create it with a select statement? I wouldn't like to use a "for"
Thanks

ChrisM (above) pointed you to an article that tells not only how to do it in a very high speed manner, but also a couple of things you can do with it. I recommend you read it.

Just to wet your appetite... try this... don't blink when you run it... you'll miss it... 😉

--===== Create and populate the Tally table on the fly

SELECT TOP 11000 --equates to more than 30 years of dates

IDENTITY(INT,1,1) AS N

INTO dbo.Tally

FROM Master.dbo.SysColumns sc1,

Master.dbo.SysColumns sc2

Once you have that, you might as well make it permanent... it's got lot's of uses... some you cannot even imagine...

--===== Add a Primary Key to maximize performance

ALTER TABLE dbo.Tally

ADD CONSTRAINT PK_Tally_N

PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100

--===== Allow the general public to use it

GRANT SELECT ON dbo.Tally TO PUBLIC

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

RBarryYoung SSC Guru Points: 143329 More actions · Answer 8

Chirag (12/2/2008)
i think i got this from some code written by Jeff Modem 🙂

BeeeeeeeeeeeeBoooooooooooShshshshshshhhhhhhhhh....

😀

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

Jeff Moden SSC Guru Points: 1004339 More actions · Answer 9

So THAT's what the helmet does! 😛

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeffrey Williams SSC Guru Points: 90303 More actions · Answer 10

RBarryYoung (12/29/2008)
Chirag (12/2/2008)
i think i got this from some code written by Jeff Modem 🙂
BeeeeeeeeeeeeBoooooooooooShshshshshshhhhhhhhhh....
😀

Hey, I was just wondering if he's 9600, 14400 or 19200.

Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”

― Charles R. Swindoll

How to post questions to get better answers faster
Managing Transaction Logs

RBarryYoung SSC Guru Points: 143329 More actions · Answer 11

Jeffrey Williams (12/29/2008)
RBarryYoung (12/29/2008)
Chirag (12/2/2008)
i think i got this from some code written by Jeff Modem 🙂
BeeeeeeeeeeeeBoooooooooooShshshshshshhhhhhhhhh....
😀
Hey, I was just wondering if he's 9600, 14400 or 19200.

19.2, you know Jeff. Nothing but the fastest will do. 😉

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]

RBarryYoung SSC Guru Points: 143329 More actions · Answer 12

Jeff Moden (12/29/2008)
So THAT's what the helmet does! 😛

Yep. allows me to talk to R2 units and other Modems. 😛

[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung [font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]