Creating an Identity column to a SELECT statement

Hi all,
I am pretty much going insane. I have tried all sorts of things and gotten
nowhere, and I'm fairly sure there exists a simple solution to my problem.
If you help me, I will be eternally in your debt.
Here is the scenario. I have a stored procedure which conains a fairly heavy
UNION query, which I'm not going to repeat here. The data comes from all
sorts of places, and the data that comes back has no unique record
identifier. I want to add one, that is, effectively add a IDENTITY column to
the query result.
I have put the data from the UNION query into a table variable, called
@.myResults (I could put it into a temp table #myResult instead, if you
care). This kind of makes my problem simpler to see, but be aware that the
source tables have no unique identifier I can use. The column "myID" I have
just made up with zero value, in case it can be used.
SELECT myID, Color FROM @.myResults -- Simplified example, this is
what I get
0 Blue
0 Red
0 Green
I want to add a column or update the myID column so that it looks like this,
counting each row
-- This is what I want. How?!?!
1 Blue
2 Red
3 Green
Simple, eh? That's what I thought.
I have tried this --
SELECT IDENTITYCOL as "myNewID",Color FROM @.myResults -- Doesn't
work
and
SELECT @.@.ROWCOUNT, Color FROM @.myResults -- Doesn't work, has number
3 on each row
what I want is something like this --
SELECT @.@.ROWNUMBER,Color from @.myResults -- Wish it existed, but doesnt as
far as I can tell
As a general thing, I'm not sure how to add an identity column to a table
that already has data in it. That's kind of what I am trying to do, but to a
select statement result.
I have even considered looping through each record in a cursor and manually
updating the int. Seems like a lot of work, and this stored procedure is
going to get hit a lot and needs to be fairly fast.
Mostly I'm just burning up because I *know* there is a simple answer to
this - I just can't see it!
Thanks in advance,
SaulLook at the IDENTITY function in Books Online; you basically want to do
something like:
SELECT IdentColumn = IDENTITY(int, 1,1),
OTHERColumns
INTO TargetTable --must be a table or temp table
FROM @.myResults
HTH
Stu|||I suggest you to insert data into a #TempTable like
Select Identity(int, 1, 1) as RowNumber, * Into #TempTableName From TableNam
e
-- That should generate record numbers for you
HTH
Ed
"Saul" wrote:

> Hi all,
> I am pretty much going insane. I have tried all sorts of things and gotten
> nowhere, and I'm fairly sure there exists a simple solution to my problem.
> If you help me, I will be eternally in your debt.
> Here is the scenario. I have a stored procedure which conains a fairly hea
vy
> UNION query, which I'm not going to repeat here. The data comes from all
> sorts of places, and the data that comes back has no unique record
> identifier. I want to add one, that is, effectively add a IDENTITY column
to
> the query result.
> I have put the data from the UNION query into a table variable, called
> @.myResults (I could put it into a temp table #myResult instead, if you
> care). This kind of makes my problem simpler to see, but be aware that the
> source tables have no unique identifier I can use. The column "myID" I hav
e
> just made up with zero value, in case it can be used.
> SELECT myID, Color FROM @.myResults -- Simplified example, this is
> what I get
> 0 Blue
> 0 Red
> 0 Green
> I want to add a column or update the myID column so that it looks like thi
s,
> counting each row
> -- This is what I want. How?!?!
> 1 Blue
> 2 Red
> 3 Green
> Simple, eh? That's what I thought.
> I have tried this --
> SELECT IDENTITYCOL as "myNewID",Color FROM @.myResults -- Doesn't
> work
> and
> SELECT @.@.ROWCOUNT, Color FROM @.myResults -- Doesn't work, has numbe
r
> 3 on each row
> what I want is something like this --
> SELECT @.@.ROWNUMBER,Color from @.myResults -- Wish it existed, but doesnt a
s
> far as I can tell
> As a general thing, I'm not sure how to add an identity column to a table
> that already has data in it. That's kind of what I am trying to do, but to
a
> select statement result.
> I have even considered looping through each record in a cursor and manuall
y
> updating the int. Seems like a lot of work, and this stored procedure is
> going to get hit a lot and needs to be fairly fast.
> Mostly I'm just burning up because I *know* there is a simple answer to
> this - I just can't see it!
> Thanks in advance,
> Saul
>
>
>|||Guys,
Thank you for your responses!!
Yes, what you suggested works, and works quite well. What I guess I don't
like about it is that this means creating a temp table to solve the problem.
But it does work, so I'm not complaining! So, thanks again!!
On the way down to lunch, I thought of another solution though, which I like
better and also works. Here's the idea - when I intially declare the table
variable (@.myResults) I define an indentity column there. Then, when I do
the insert into.., I don't insert into that ID column, and it takes care of
creating the identity. What I preffer about this solution is that the
identity is built the first time when the data is being inserted.
eg
declare @.myResults table (my_ID int identity(1,1) , colour varchar(20))
insert into @.myResults
SELECT colour
FROM table1
UNION
SELECT colour
FROM table2
Of course, my real world query is vastly more complicated, but it's for
illustration purposes.
- Saul
"Ed" <Ed@.discussions.microsoft.com> wrote in message
news:7088713E-1340-4D8D-9328-C8F0DEAF251B@.microsoft.com...
>I suggest you to insert data into a #TempTable like
> Select Identity(int, 1, 1) as RowNumber, * Into #TempTableName From
> TableName
> -- That should generate record numbers for you
> HTH
> Ed
>
> "Saul" wrote:
>|||There is no "simple" way of doing this, and there may not every be. The
issue here is that you need to something to order the data on. If you have
some unique value, and you want to add a sequence number, you can do
something like:
select 'Blue' as color
into #testtable
union all
select 'Red'
union all
select 'Green'
select color, (select count(*) from #testTable as t2 where t2.color <=
#testTable.color) as rowNumber
from #testTable
order by 2
To do a non-sortable order, you will need to build the data first (don't
expect the order you see from a select to always be the order of the
results. There are no guarantees with row order in a relational database.)
(note, in 2005 there will be an easier way to do this, but the same
limitations do exist.)
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"Arguments are to be avoided: they are always vulgar and often convincing."
(Oscar Wilde)
"Saul" <sbryan@.nsw.counterpoint.com.au> wrote in message
news:4355a839$0$1360$c30e37c6@.ken-reader.news.telstra.net...
> Hi all,
> I am pretty much going insane. I have tried all sorts of things and gotten
> nowhere, and I'm fairly sure there exists a simple solution to my problem.
> If you help me, I will be eternally in your debt.
> Here is the scenario. I have a stored procedure which conains a fairly
> heavy UNION query, which I'm not going to repeat here. The data comes from
> all sorts of places, and the data that comes back has no unique record
> identifier. I want to add one, that is, effectively add a IDENTITY column
> to the query result.
> I have put the data from the UNION query into a table variable, called
> @.myResults (I could put it into a temp table #myResult instead, if you
> care). This kind of makes my problem simpler to see, but be aware that the
> source tables have no unique identifier I can use. The column "myID" I
> have just made up with zero value, in case it can be used.
> SELECT myID, Color FROM @.myResults -- Simplified example, this is
> what I get
> 0 Blue
> 0 Red
> 0 Green
> I want to add a column or update the myID column so that it looks like
> this, counting each row
> -- This is what I want. How?!?!
> 1 Blue
> 2 Red
> 3 Green
> Simple, eh? That's what I thought.
> I have tried this --
> SELECT IDENTITYCOL as "myNewID",Color FROM @.myResults -- Doesn't
> work
> and
> SELECT @.@.ROWCOUNT, Color FROM @.myResults -- Doesn't work, has
> number 3 on each row
> what I want is something like this --
> SELECT @.@.ROWNUMBER,Color from @.myResults -- Wish it existed, but doesnt
> as far as I can tell
> As a general thing, I'm not sure how to add an identity column to a table
> that already has data in it. That's kind of what I am trying to do, but to
> a select statement result.
> I have even considered looping through each record in a cursor and
> manually updating the int. Seems like a lot of work, and this stored
> procedure is going to get hit a lot and needs to be fairly fast.
> Mostly I'm just burning up because I *know* there is a simple answer to
> this - I just can't see it!
> Thanks in advance,
> Saul
>
>