Add a row number column while creating a temp table

Question

Post reply

Add a row number column while creating a temp table

rightfield

SSCrazy

Points: 2418
More actions
January 24, 2011 at 1:44 pm

#230832

Hi, I would like to add a row number column while doing a SELECT INTO statement.
I will have a set of stored procedures that will be added to as they are discovered and they will always be named like MyRule1, MyRule2, MyRule3, etc. I want to be able to dynamically loop through and execute each one. My initial query is
SELECT [name] INTO #RuleList FROM sys.objects WHERE type='P' AND name LIKE 'MyRule%'
I could then use a WHILE loop to retrieve each [name] and then EXECUTE the procedure. What is way to get a row number column into #RuleList so that the result is a table like this:
#RuleList ( rownumber int, name varchar(500) )
Thanks

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

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

RP_DBA SSCertifiable Points: 5116 More actions · Answer 1

Check out the ROW_NUMBER ranking function in BOL: http://msdn.microsoft.com/en-us/library/ms186734.aspx

_____________________________________________________________________
- Nate

@nate_hughes

Derrick Smith SSChampion Points: 10833 More actions · Answer 2

SELECT [RN] = Row_number() OVER (PARTITION BY NAME ORDER BY NAME)

,[name]

INTO #RuleList

FROM sys.objects

WHERE type = 'P'

AND name LIKE 'MyRule%'

rightfield SSCrazy Points: 2418 More actions · Answer 3

The result was every row number as 1 when I used the example code of

SELECT [RN] = Row_number() OVER (PARTITION BY NAME ORDER BY NAME)

,[name]

INTO #RuleList

FROM sys.objects

WHERE type = 'P'

AND name LIKE 'MyRule%'

Lowell SSC Guru Points: 323518 More actions · Answer 4

another option is to use the IDENTITY() function, which is allowed when you use the SELECT INTO format to create an identity column in the dynamically created table:

SELECT

identity(int,1,1) as MyID, --function creates the identity() column only allowed with INTO statements.

[name] INTO #RuleList

FROM sys.objects

WHERE type='P'

AND name LIKE 'MyRule%'

/*

CREATE TABLE [dbo].[#RULELIST] (

[MYID] INT IDENTITY(1,1) NOT NULL,

[NAME] SYSNAME NOT NULL)

*/

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

rightfield SSCrazy Points: 2418 More actions · Answer 5

I took out the PARTITION BY name and it worked. Thank you.

Derrick Smith SSChampion Points: 10833 More actions · Answer 6

Whoops - sorry about that. Force of habit on the PARTITION BY.