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

Creating a Boolean EvalExpression comparing dates

To the experts in the field:

There is probably a very simple solution that is avoiding my grasp.

I have a For Loop which I want to execute as long as a variable called BeforeRunDt = CurrentDate. Both are DateTime data types and I am using the following expression:

@.BeforeRunDt==@.CurrenDate

I get an error stating "Cannot convert expression value to propeerty type"

I understand that the result of the expression should be a boolean value but am just struggling on how to create it.

Thanks!

I believe that the following will work:

@.BeforeRunDt == @.CurrentDate ? True : False

Edit: You would set the variable to type boolean, and evaluate as expression to true with the above as the expression...

|||

Wow! Now I feel really stupid. After all the fuss I figured that I needed a For Each Loop instead and so I am back at square one trying to learn how to use that transform.

Thank you very much for taking the time to respond to my question!

|||

desibull wrote:

Wow! Now I feel really stupid. After all the fuss I figured that I needed a For Each Loop instead and so I am back at square one trying to learn how to use that transform.

Thank you very much for taking the time to respond to my question!

I would still have expected the expression in your first post to work.

Try replacing it with

(DT_BOOL)(@.BeforeRunDt==@.CurrenDate)

and see if it works.

cheers

Jamie

|||I was slightly surprised that his first expression wouldn't work as well... but if it isn't cast to the proper type that kind of makes sense I suppose...|||

EWisdahl wrote:

I was slightly surprised that his first expression wouldn't work as well... but if it isn't cast to the proper type that kind of makes sense I suppose...

Yeah. it could be similar to the issue talked about here:

NULLs in expressions gotcha

(http://blogs.conchango.com/jamiethomson/archive/2006/10/12/SSIS_3A00_-NULLs-in-expressions-gotcha.aspx)

-Jamie

Creating a Auto Increment which contains Numbers & Text

Hi,
Please can you let me know the best solution for creating a primary key which automatically increments by 1 each time a record is added. My current Primary key is of type "Int" which increments by 1 each time, but I would like my primary key to contain "ABC" before the 1. So each time a record is added I would like to see:-
ABC000001
ABC000002
ABC000003
Etc, Etc
I am using SQL Server 2000 and creating an ASP.Net application, will I need to write code in a Stored Procedure to do this?
Regards,
BrettI would just create a view that exposes the alpha you want plus the IDENTITY column, formatted as you wish.|||

Douglas' solution is a horrible hack. It is not scalable (what happens when you want "BCD" and "ABC") and requires having two keys -- the "real" key and the "identity" key.
You were on the right track with a stored procedure. I don't know what you're trying to model ... so I'm going with Accounts.
Your proc should look like:
PROCEDURE Create_New_Account ( @.Account_Number CHAR(8) OUT, @.Salesperson_Number CHAR(5), (... other required fields) )
I'd recommend pre-creating your identifiers, and doing a SELECT / DELETE out of the Account_Numbers table. If that won't fly, just SELECT MAX(Account_Number) from your Accounts table, parse out the string and increment the number part.

|||

I would disagree with the "Horrible hack" characterization. If there was a column [Prefix] and a column with the identity, the [Prefix] could be whatever you want. I saw a reference to Auto Increment and was thinking about Access rather than SQL Server (yes, of course I realize the post was on the SQL Server forum).

If you can ensure all access to inserting data is through the stored procedures, then of course use that. method.

What is being attempted (adding meaning of some sort to an identifier) is not often a wonderful thing.

|||You're right; it's ideal to have to have both parts of the key in the table instead of combining it into one.
But I won't concede to "Horrible Hack" being a mischaraterization. Everytime I've seen this done ... it's a horrible hack ... here's my favorite example ...
Order_Numbers were defined something as : (Order_Date + Order_Seq) + XOR Parity Shift. This was to ensure order_nums were not predictable (from end users) and were verifiable.
The programmer who implemented this requirement used this schema:
Orders_Base = TABLE (Order_ID, Order_Date, Order_Seq, ...)
Orders = VIEW( SELECT complex_generator_fn( Order_Date, Order_Seq) as Order_Num, Order_Date, ... )
Guess how fun that got when they actually used the system to place orders. And tried to query on the non-indexed Order_Num.
They first implemented decode logic in the Middle tier to get Seq + Date. Surprise surprise, that didn't quite work out so well either.
Final iteration (and still as it exists today):
Orders_Base = TABLE( Order_ID, Order_Date, Order_Seq )
Order_Numbers = TABLE( Order_ID, Order_Number )
Oh, and warehousing and analytics is a COMPLETE nightmare.