luke luke - 1 year ago 107
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

Answer Source

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 
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download