adding identity column dynamically

adding identity column dynamically

Post by Sam » Thu, 17 Nov 2005 23:33:03


Hi,
In my stored procedure I'm doing a SELECT on
INFORMATION_SCHEMA.TABLE_CONSTRAINTS. However there is no unique id on
this table, so I was wondering if it was possible to add it dynamically
in my SELECT, so that I would assign a unique id to each record
returned by my SELECT?

Thanks for your support.
 
 
 

adding identity column dynamically

Post by Robert Kle » Fri, 18 Nov 2005 00:32:04


Where do you need that for? Maybe this helps:

http://www.yqcomputer.com/ ;EN-US;q186133

robert

 
 
 

adding identity column dynamically

Post by Erland Som » Fri, 18 Nov 2005 00:34:44

Sam ( XXXX@XXXXX.COM ) writes:

There should be no need for this. There is a unique key in
(CONSTRAINT_CATALOG, CONSTRAINT_SCHEMA, CONSTRAINT_NAME).

To get an IDENTITY column you could bounce over a temp table with:

SELECT ident = IDENTITY(int, 1, 1), CONSTRAINT_CATATLOG, ...
INTO #temp
FROM INFORMATION_SCHEMA.TABLE_CONSTRAINTS
WHERE ...

SELECT * FROM #temp

But, as I said there is no need to do this.


--
Erland Sommarskog, SQL Server MVP, XXXX@XXXXX.COM

Books Online for SQL Server 2005 at
http://www.yqcomputer.com/
Books Online for SQL Server 2000 at
http://www.yqcomputer.com/
 
 
 

adding identity column dynamically

Post by Sam » Fri, 18 Nov 2005 00:39:40

Thanks. That's what I needed.
I don't want to use the triplet as an id, i want a unique number, it's
easier to handle on the client code side.