Identity in a SELECT


December 19, 2018 by Kenneth Fisher

Always be learning right? So when I saw this I had to go HU?! That’s new. What’s going on here?


Now, looking at it a bit more closely you’ll see that this is a function call, not just a property. Now, in my research for this post I did find where I’d mentioned this function briefly in my somewhat comprehensive identity post. Technically I didn’t mention so much as it was mentioned to me in the comments so I added it to the list. I guess I either didn’t look at it closely enough at the time or it’s just one of those cases where I forgot. Either way, it’s worth highlighting now.

So what does the identity function do? It lets you add an identity column to the output of a SELECT INTO. So when we run the following code:

INTO temp
FROM sys.databases

We get a table with the following format:

See? Identity column! You can pass in the datatype, seed, and increment just like when setting an identity property. And as with any calculated column when using an INTO you have to give it a name. Unfortunately, you can’t use an existing column to populate the column (database_id in the above sample for example). If you want to do that you’ll have to create the table with 0 rows and then set IDENTITY_INSERT on and do an INSERT INTO.

4 thoughts on “Identity in a SELECT

  1. well that’s easy. thanks for sharing

  2. […] Kenneth Fisher shares something he learned recently about the IDENTITY function: […]

  3. IM Fletcher says:

    Wow! That’s something new (to me). I always select into first and then follow up with an alter table to add the identity. Thanks for posting.

Leave a Reply

Fill in your details below or click an icon to log in: Logo

You are commenting using your account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Enter your email address to follow this blog and receive notifications of new posts by email.

Join 3,753 other subscribers

Follow me on Twitter

ToadWorld Pro of the Month November 2013
%d bloggers like this: