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

• RP_DBA

  SSCertifiable

  Points: 5116

  More actions

  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

• Derrick Smith

  SSChampion

  Points: 10833

  More actions

  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%'

• rightfield

  SSCrazy

  Points: 2418

  More actions

  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%'

• Lowell

  SSC Guru

  Points: 323522

  More actions

  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!

• rightfield

  SSCrazy

  Points: 2418

  More actions

  January 24, 2011 at 2:12 pm

  #1277470

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

• Derrick Smith

  SSChampion

  Points: 10833

  More actions

  January 24, 2011 at 2:13 pm

  #1277471

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