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?

score 4 · Accepted Answer · answered Feb 26 '10 at 20:08

4

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.

answered Feb 26 '10 at 20:08

HLGEM

Nice, I didn't know you could do that. + 1 – cmsjr Feb 26 '10 at 20:13
1

If there was a refuse to be accepted answer feature, I would be using it right now. I hope the OP revisits this, your approach is definitely more elegant. – cmsjr Feb 26 '10 at 20:18
+1, I tried numerous ways to specify an identity, but couldn't get the syntax to work, best I could find was the new_id(). This makes my answer wrong. – KM. Feb 26 '10 at 20:20
I came across this little gem accidentally while looking for something else in BOL about 7 or 8 years ago. It's come in mighty handy ever since. – HLGEM Feb 26 '10 at 20:25
duh! I have an example of this exact thing in another answer I posted TODAY (right near the top): http://stackoverflow.com/questions/2341374/sql-comma-delimted-column-to-rows-then-sum-totals/2341510#2341510 – KM. Feb 26 '10 at 20:30
+1 I too had no idea. Great solution. I could have used this thousands of times. – Brian Spencer Feb 26 '10 at 21:47

score 1 · Answer 2 · answered Feb 26 '10 at 19:49

1

You can add an identity after the select into completes.

Alter Table ProdClass add Id int Identity Primary Key

answered Feb 26 '10 at 19:49

cmsjr

2 Answers2