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

I need to duplicate selected rows with al开发者_开发知识库l 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 ?

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 ....

If it is a pure copy (minus the ID field) then the following will work (replace 'NameOfExistingTable' with the table you want to duplicate the rows from and optionally use the Where clause to limit the data that you wish to duplicate):

SELECT * 
INTO #TempImportRowsTable
FROM (
    SELECT *
    FROM [NameOfExistingTable]
    -- WHERE ID = 1
) AS createTable

-- If needed make other alterations to the temp table here

ALTER TABLE #TempImportRowsTable DROP COLUMN Id

INSERT INTO [NameOfExistingTable]
SELECT * FROM #TempImportRowsTable

DROP TABLE #TempImportRowsTable

If you're able to check the duplication condition as rows are inserted, you could put an INSERT trigger on the table. This would allow you to check the columns as they are inserted instead of having to select over the entire table.