Worth Corner, Crawley, RH10 7SL

Support Hours - Mon-Sat, 8.00-18.00

Welcome to our SQL Server blog

This blog was created in order to pass on some of our knowledge and advice in the hope that the community might find it useful.

Obviously Microsoft SQL Server is our area of expertise and therefore this is our most thorough blog with everything from the basic through to the the incredibly advanced and, as such, we should have something for everyone within our pages.


SELECT INTO with IDENTITY
Published: Nov 27, 2020
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.

Leave a Comment
Your email address will not be published. All fields are mandatory.
NB: Comments will only appear once they have been moderated.

SQL  World  CEO
Kevin  Urquhart

iPhone Selfie

I am a SQL Server DBA, Architect, Developer, Trainer, and CEO of SQL World. This is my blog in which I’m simply trying to share my SQL knowledge and experiences with the world.

Categories


© Copyright 2020 SQLTraining Ltd.