luke luke - 1 month ago 4x
SQL Question

add id column on INSERT INTO statement

I need to have a AI ID column added when I do a:

SELECT * INTO newTable FROM(...


I am doing it from a query with multiple joins and need to have a ID generated for every row that gets inserted into a new table that is being created from the query.

I am using SSMS 2012


In SQL SERVER we have IDENTITY function

Is used only in a SELECT statement with an INTO table clause to insert an identity column into a new table. Although similar, the IDENTITY function is not the IDENTITY property that is used with CREATE TABLE and ALTER TABLE.

Try something like this

SELECT identity(int,1,1) as ID_Num,* 
INTO newTable