[SOLVED] Is there a way to quickly duplicate record in T-SQL?

Is there a way to quickly duplicate record in T-SQL?

I need to duplicate selected rows with all the fields exactly same except ID ident int which is added automatically by SQL.

What is the best way to duplicate/clone record or records (up to 50)?

Is there any T-SQL functionality in MS SQL 2008 or do I need to select insert in stored procedures ?

Solution

The only way to accomplish what you want is by using Insert statements which enumerate every column except the identity column.

You can of course select multiple rows to be duplicated by using a Select statement in your Insert statements. However, I would assume that this will violate your business key (your other unique constraint on the table other than the surrogate key which you have right?) and require some other column to be altered as well.

Insert MyTable( ...
Select ...
From MyTable
Where ....