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?
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