SELECT INTO IDENTITY
-
Posted on March 28, 2012 by Derek Dieter
-
2
Adding an identity column to a SELECT..INTO query is probably something I use nearly on a daily basis. I’m providing it here merely for reference purposes. It useful when you want to loop through a temporary table.
[cc lang=”sql”]
SELECT
id = IDENTITY(int,1,1)
,FirstName = pc.FirstName
,LastName = pc.LastName
INTO #person_contact
FROM Person.Contact pc
WHERE EmailPromotion = 2
ORDER BY pc.LastName
[/cc]
- The first parameter for the IDENTITY function is that data type. It must be of data type int, bigint, smallint, tinyint, or decimal or numeric with a scale of 0, and constrained to be nonnullable.
- The second parameter is the seed (or starting) value.
- The third parameter is the increment.
An alternative method is to use the ROW_NUMBER window function. This can actually be a bit more pliable in certain situations because of it’s optional partitioning function. The obvious difference here though is that any rows subsequently added to the table do not get an identity value.
[cc lang=”sql”]
SELECT
id = ROW_NUMBER() OVER (ORDER BY pc.LastName)
,FirstName = pc.FirstName
,LastName = pc.LastName
INTO #person_contact2
FROM Person.Contact pc
WHERE EmailPromotion = 2
[/cc]
Note that with the IDENTITY method, Microsoft does not guarantee that the identity will be in the correct order when performing an order by clause, especially when using TOP or ROWCOUNT.
- Comments (RSS)
- Trackback
- Permalink
a million thanks! This is just what I was looking for in order to specify identity on a dynamically created Audit table. so many of these good old SQL 2000 features are almost forgotten today.
Rob good question. I’ve been cuuoris about that myself, but since it costs money to ramp up a new server and do performance testing, I haven’t bothered. I’ve been waiting until I see word from them that there’s a better-performing storage subsystem available, or that they’ve improved it somehow. Have you seen anything to that effect?
I have a doubt on identity function.If i use identity function in select statement it generates numbers orderly.suppose i use identity function in a select statement on rollnum in a student table till 10 rows it generates numbers in a order like 1 to 10 but if i want to display from 11th record as 108 ,109 or some other numbers.What to do??
He that is without sin among yuothat ranks among the least smart comments I’ve ever seen – anywhere.Hey – we all make mistakes, ignore them, don’t spread the word. People probably won’t exploit this stuff.the code you refer to isn’t “sql injection”, most of it was actually demonstrating bind variables. The one utility “dump_*” used to dump a query to a flat file is an obvious “beware to whom you grant that”sorry – but sql injection is such a problem – a huge one – that everyone needs to have it pointed out when it appears in real code like that.
Just be aware that MS doesn’t guarantee that the order of the IDENTITY will match the order of the ORDER BY. For example, if parallelism kicks in, you could be in for quite a surprise. You can overcome that part of the problem by using OPTION(MAXDOP 1) in the SELECT/INTO.
Also, if the ORDER BY is based on something indeterminant such as GETDATE() (not sure why any would do an ORDER BY using GETDATE(), but I have seen someone do it), then the chances are really high that the numbering created by IDENTITY will not be in the same order as the ORDER BY.
That, not withstanding, I’ve not seen it fail if you limit parallism and also make sure that the ORDER BY is determinant.
Thank you Jeff, good point.
