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 an IDENTITY column in a view

Is it possible to create an IDENTITY column in a view? I would like to have a column which will always start at 1 and increment 1 for every record in the view.
Thanks!
CSThere might be another way to get what you want. It depends on your data. For example if you have a table that has unique rows you could write something like this:

SELECT
COUNT(*) AS ID,
A.Activity_Type_Ky
FROM
Activity_Type AS A
JOIN Activity_Type AS B
ON A.Activity_Type_Ky > B.Activity_Type_Ky
GROUP BY
A.Activity_Type_Ky

You could also use a function or stored procedure with a temporary table to get what you want if you are not limited to a view.|||I suggest you use a stored procedure to:

1. create a temporary table that includes the identity column
2. insert all the records of your view into the temporary table using single T-SQL statement
3. select * from the temprary table

in sql server 2000 you don't need to drop the temp table

Creating a copy of a record

What is the simplest way to create a copy of a row?
The table in question has a primary key that is an identity field, which
will obviously need to have a different value, but otherwise I want to be
able to create a row that is identical to another one.
AFAIK Select Into only copies rows into a new table.
I could obviously retrieve each field from the original row (bar the primary
key) and INSERT a new record with this information, but since there are a
fair few columns, I wondered if there might be a simpler one-line SQL
instruction that will do the job...
Thanks in advance
Chris
cjmnews04@.REMOVEMEyahoo.co.uk
[remove the obvious bits]Think again. Why would you ever want to duplicate a row in a table?
(even with a different IDENTITY key).
In principle:
INSERT INTO YourTable (col1, col2, ...)
SELECT col1, col2, ...
FROM YourTable
WHERE /* some row */ ;
However, this ought to fail on a key violation because IDENTITY should
never be the only key of a table. Rethink your requirement and your
table design.
David Portas
SQL Server MVP
--|||Hi
CREATE TABLE #Test
(
pk INT NOT NULL PRIMARY KEY,
col CHAR(1),
col1 INT
)
INSERT INTO #Test VALUES (1,'a',20)
--Copy Row
INSERT INTO #Test
SELECT (SELECT COALESCE(MAX(pk),0)+1 FROM #test) AS pk,
col,col1 FROM #Test WHERE pk=1 --change to variable
SELECT * FROM #test
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:%23cT1aMdvFHA.3688@.tk2msftngp13.phx.gbl...
> What is the simplest way to create a copy of a row?
> The table in question has a primary key that is an identity field, which
> will obviously need to have a different value, but otherwise I want to be
> able to create a row that is identical to another one.
> AFAIK Select Into only copies rows into a new table.
> I could obviously retrieve each field from the original row (bar the
> primary key) and INSERT a new record with this information, but since
> there are a fair few columns, I wondered if there might be a simpler
> one-line SQL instruction that will do the job...
> Thanks in advance
> Chris
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1127214710.180280.324430@.g43g2000cwa.googlegroups.com...
> Think again. Why would you ever want to duplicate a row in a table?
> (even with a different IDENTITY key).
> In principle:
> INSERT INTO YourTable (col1, col2, ...)
> SELECT col1, col2, ...
> FROM YourTable
> WHERE /* some row */ ;
> However, this ought to fail on a key violation because IDENTITY should
> never be the only key of a table. Rethink your requirement and your
> table design.
>
I can see where you are coming from, but I'm afraid my requirement is
genuine & valid.
The table in question hold orderlines. In this example an orderline is being
closed off, but a new copy of the line is to be added to the order.
Therefore, I hoped to copy original orderline (with a different PK
obviously) and then Update the original to close it off. [Obviously this is
a simplistic explanation).
As you can see, in this case the table design is fine. And the requirement,
IS the requirement. But how we satisfy the requirement is the question...
and hence my post.|||"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:%23gxDjRdvFHA.612@.TK2MSFTNGP10.phx.gbl...
> Hi
>
> CREATE TABLE #Test
> (
> pk INT NOT NULL PRIMARY KEY,
> col CHAR(1),
> col1 INT
> )
> INSERT INTO #Test VALUES (1,'a',20)
> --Copy Row
> INSERT INTO #Test
> SELECT (SELECT COALESCE(MAX(pk),0)+1 FROM #test) AS pk,
> col,col1 FROM #Test WHERE pk=1 --change to variable
> SELECT * FROM #test
Uri,
Thanks for this.
Q. Will this still work if my PK is an Identity field?
Chris|||> As you can see, in this case the table design is fine.
I don't see that. There are serious problems with tables that have only
an IDENTITY key. The consequences of storing duplicate data make it
very difficult (perhaps impossible in some conditions) to validate and
integrate data without logical keys. Also, there are practical
programming issues in TSQL because of the way IDENTITY values are
assigned in multiple row INSERTs.
Putting that aside, why store redundant data? If the row is going to be
modified anyway as you have said then why not insert the modified row
instead of duplicating an existing one and incurring the overhead of an
extra update? Seems like you are paying a high price in integrity and
performance for no discernable benefit.
David Portas
SQL Server MVP
--|||CJM
I think David has
already answered this question , however you are free to try it.
"CJM" <cjmnews04@.newsgroup.nospam> wrote in message
news:u4WSrhdvFHA.3740@.TK2MSFTNGP14.phx.gbl...
> "Uri Dimant" <urid@.iscar.co.il> wrote in message
> news:%23gxDjRdvFHA.612@.TK2MSFTNGP10.phx.gbl...
> Uri,
> Thanks for this.
> Q. Will this still work if my PK is an Identity field?
> Chris
>|||> I can see where you are coming from, but I'm afraid my requirement is
> genuine & valid.
> The table in question hold orderlines. In this example an orderline is
> being closed off, but a new copy of the line is to be added to the order.
WHY? What is different about the row? Why does it need to be deleted and
re-inserted?
A|||"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:1127217457.000463.24290@.g44g2000cwa.googlegroups.com...
> I don't see that. There are serious problems with tables that have only
> an IDENTITY key. The consequences of storing duplicate data make it
> very difficult (perhaps impossible in some conditions) to validate and
> integrate data without logical keys. Also, there are practical
> programming issues in TSQL because of the way IDENTITY values are
> assigned in multiple row INSERTs.
>
Well I'll have to reserve judgement until I have digested all this... (which
I will).
What alternative would you offer instead of using an identity field?
I've not noticed any significant issues with Identity fields, but I'm always
open to improving my knowledge and my techniques...

> Putting that aside, why store redundant data? If the row is going to be
> modified anyway as you have said then why not insert the modified row
> instead of duplicating an existing one and incurring the overhead of an
> extra update? Seems like you are paying a high price in integrity and
> performance for no discernable benefit.
>
What data is redundant? The original record? No, not so. It remains an
essential part of the order. It would take too long to truly put this into
the right context, but suffice to say that the new row is NOT replacing the
old row. Both will co-exist and both are essential.
I've adapted the INSERT INTO statement that you suggested, and I now have
one command that achieves what I need. I don't see how it could get any more
efficient.|||-- if ur table has huge no of columns, u can try
select * into #t from tbl_name where ...
insert into tbl_name
select * from #t
drop table #t
Rakesh
"CJM" wrote:

> What is the simplest way to create a copy of a row?
> The table in question has a primary key that is an identity field, which
> will obviously need to have a different value, but otherwise I want to be
> able to create a row that is identical to another one.
> AFAIK Select Into only copies rows into a new table.
> I could obviously retrieve each field from the original row (bar the prima
ry
> key) and INSERT a new record with this information, but since there are a
> fair few columns, I wondered if there might be a simpler one-line SQL
> instruction that will do the job...
> Thanks in advance
> Chris
> --
> cjmnews04@.REMOVEMEyahoo.co.uk
> [remove the obvious bits]
>
>

Create View

how to create view in sql server 2005 that recognize Identity And Primary key of base tableI don't understand what the requirement is, over any normal view. See CREATE VIEW in Books Online perhaps? Why is this related to SQL Server Integration Services, the title of this forum?|||

SELECT COLS.table_schema
,COLS.Table_name
,COLS.COLUMN_NAME
,cols.CONSTRAINT_NAME
,ac.IS_identity
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE AS COLS
INNER JOIN INFORMATION_SCHEMA.TABLE_CONSTRAINTS CONS
ON COLS.CONSTRAINT_NAME = CONS.CONSTRAINT_NAME
JOIN sys.all_columns ac
ON OBJECT_NAME(ac.object_id) = COLS.table_name
AND ac.name=COLS.COLUMN_NAME
WHERE CONS.CONSTRAINT_TYPE LIKE 'PRIMARY KEY'
ORDER BY COLS.CONSTRAINT_NAME, COLS.ORDINAL_POSITION

as i wrote this pretty quick and didnt test it fully.. using it is on your own risk ;)

Guldmann, platon.dk