SELECT INTO with IDENTITY
Published: Nov 28, 2021
This is a fun one because I’ve seen numerous people struggling with identity columns in temp or freshly generated tables.

For example, let’s take a look at the Person.Person table in AdventureWorks:


Now, if we just want to make a copy of this table then it’s nice and simple:

use AdventureWorks2012
go

drop table if exists testPerson
go

select *
into testPerson
from person.person
go


That’s it… we’re done.

BUT what if we want to add an identity column to the table? Well, we can’t. There are no “alter table” expressions which allow the addition of an identity column after declaration. So how can we do this?

The way in which I see nearly all SQL Developers do this is to go to the trouble of scripting out the whole table using management studio, editing all the defaults and calculated columns, removing primary keys and extended properties, changing the name of the table etc etc. THEN they add the identity column to the definition and they’re ready for an INSERT INTO statement.

But, if you want the shortcut then let’s try this:

use AdventureWorks2012
go

drop table if exists testPerson
go

select identity(int, 1, 2) as myIdentityColumn, *
into testPerson
from person.person
go

select top 10 *
from testPerson
go



As you can see, we now have our copy of the table complete with Identity column and in the format we specified (ie. starting at 1 and incrementing in 2s, therefore 1, 3, 5, 7, 9 etc.)

This is much simpler and should make your code a lot shorter and easier to read.

Comments:
NB: Comments will only appear once they have been moderated.