January 24, 2011 at 1:44 pm
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
January 24, 2011 at 1:48 pm
Check out the ROW_NUMBER ranking function in BOL: http://msdn.microsoft.com/en-us/library/ms186734.aspx
_____________________________________________________________________
- Nate
January 24, 2011 at 2:00 pm
SELECT [RN] = Row_number() OVER (PARTITION BY NAME ORDER BY NAME)
,[name]
INTO #RuleList
FROM sys.objects
WHERE type = 'P'
AND name LIKE 'MyRule%'
January 24, 2011 at 2:05 pm
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%'
January 24, 2011 at 2:05 pm
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
January 24, 2011 at 2:12 pm
I took out the PARTITION BY name and it worked. Thank you.
January 24, 2011 at 2:13 pm
Whoops - sorry about that. Force of habit on the PARTITION BY.
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy