Insert Into + Identity

[vlitim76]

I want to be able to copy one row in a table to the same table. The table has 10 normal columns and an identity column. Idealy I would like to so something like this,

insert into #table
select * from #table WHERE id = 1

so that if new columns are added to the table they will be copied across without having to recode.

But because it has an idenity column this isn't possible. Is there any way of doing this without listing out every column in the select and insert?

cheers

Tim

 

[Catadmin]

Without turning off the identity value? I don't think so. I think even setting Identity_Insert to On would screw up the table if you try to copy an identity value that already exists.

Anyone else done this before?



Catadmin - MCDBA, MCSA
"No, no. Yes. No, I tried that. Yes, both ways. No, I don't know. No again. Are there any more questions?"
-- Xena, "Been There, Done That"

 

[SQLSister]

No you must list every column, but in QA you can use the object browser to find the columns and drag them over rather than typing. If you open the table in the object browser and open the columns so that it shows all the columns, you then drag the word columns over to the query and magically all the columns will appear. Then just delte the refernce to the identity column (and add carriage reutrns so that you can see all the columsn as it puts them on one line) and you are done. Doesn't really take much longer than typing select * and there are no misspelling typos inthe column names.

Questions about posting. See faq183-874