Post reply

Add a row number column while creating a temp table

  • 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

  • January 24, 2011 at 1:48 pm

    #1277454

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

    _____________________________________________________________________
    - Nate

    @nate_hughes

  • January 24, 2011 at 2:00 pm

    #1277462

    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

    #1277465

    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

    #1277466

    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!

  • January 24, 2011 at 2:12 pm

    #1277470

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

  • January 24, 2011 at 2:13 pm

    #1277471

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

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

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