identity column

I am instructed to add an identity column to rows of data in a table.  A simple 1, 2, 3 integer would be fine, and of course, when information is inserted, you don’t specify the identity and it will bee automatically added to the table.  Something like

create table myTable (
 id int identity(1,1)  not null,
 , myData varchar(10)
 )
Insert into myTable (myData) "abc"

this results in 1, “abc” in the table

But,  you say “i want to create the table from a select into, NOT an insert?  Now what?

I got this from  on sqlserverplanet.com

SELECT id = IDENTITY(INT,1,1)
 ,FirstName = pc.FirstName
 ,LastName = pc.LastName
INTO #person_contact  FROM Person.Contact pc

You are getting the first and last name into the (in this case) temp table and adding an identify column on the fly.  Notice that subsequent inserts of first and last name will automatically add the id entry, which is of type int, started with 1 and increments by 1 on each row and for each insert. You can also use row_number() over…  like this

SELECT   id  = ROW_NUMBER() OVER (ORDER BY pc.LastName)
    ,FirstName  = pc.FirstName
    ,LastName   = pc.LastName
INTO #person_contact2 FROM Person.Contact pc
Advertisements