Need help with Select Into

Question

I'm trying to create my first data mart. I am creating my dimension table with a "Select Into"

The code is as follows:

select distinct fpc_number, fpc_desc 
into m2mdata01dw..ProdClass
from m2mdata01..INPROD

How can I set up a autonumber primary key in this situation?

Answer 1

You can add an identity after the select into completes.

Alter Table ProdClass add Id int Identity Primary Key

Answer 2

select *, identity (int)  as myid  
into #temp
from mytable

This is the better solution than alter table I think, create the identity at the time you do the selct into.