Creating a duplicate record in a table with an identity field

[hollander]

I've searched as many ways as I can and have not found a solution to this, so I'm guessing it can't be done (at least not easily). However, I figured I'd through it out there and see if I'm missing something obvious.

I'm looking for a SQL statement that will allow me to INSERT INTO tbl SELECT * FROM tbl WHERE id=1, where tbl has an identity field. Naturally, this statement produces an error because you can't insert a value into the identity field. I know I could list out every field twice, but I was hoping for a simpler solution.

Thanks.

 

[bborissov]

Nope, there is no easiest way
Even if you SET IDENTITY_INSERT [Table name here] ON you must use field list.

Borislav Borissov
VFP9 SP1, SQL Server 2000/2005.

 

[vongrunt]

Lazy and practical

Um... even without identity column, what will happen if source table has these columns:

foo, bar, blah

And target table has

bar, blah, foo

?

Btw. that drag&drop thingy from Query Analyzer (Object Browser) can make life easier in this case.

------
[small]<this is sig>
select stuff(stuff(replicate('<P> <B> ', 14), 109, 0, '<.'), 112, 0, '/')
</this is sig>[/small]