3

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 ?

feronovak
  • 2,687
  • 6
  • 35
  • 54

3 Answers3

2

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
d219
  • 2,707
  • 5
  • 31
  • 36
2

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 ....
Thomas
  • 63,911
  • 12
  • 95
  • 141
  • This answer would be more useful if the "..." were replaced with some example content. Someone that doesn't know much about SQL and is trying to learn might not know what belongs in those places. – Troy Jun 29 '22 at 00:40
  • Really? Even newest of SQL users can't interpret that the ellipse represents the remainder of the relevant statement? Yes, it does make it less amenable to copy/paste but on the other hand that exercise may help them to learn the pattern rather than just the answer. – Thomas Oct 19 '22 at 21:59
  • There's a worked out example in this question https://stackoverflow.com/questions/11331573/copy-row-but-with-new-id, which basically deals with the same issue. I do not want to be part of the discussion, both parties make valid points. If users are still struggling after the answer of Sir Thomas, they might consider follow this link, but it is a good advice to try to build your own lines of code using the template/pattern as shown in the above answer. – qqtf Oct 20 '22 at 19:28
  • Also look at the details of the answer by Thomas. First line Insert MyTable( ... with (. Third line From MyTable without (. It's logic, but easily looked over. – qqtf Oct 20 '22 at 19:43
  • I can't speak for others, but I can tell you how I read this answer. Up to that point I had only utilized the INSERT statement with the "INSERT INTO" syntax so when I saw the "INSERT MyTable(" it looked like the definition of columns on a table. Without seeing syntactically correct complete SQL in those places it was difficult for me to figure out how to get from this to the end goal. I was left with more questions like "Do I have to explicitly list each of the 50+ columns on the table I'm working with?". I'm not interested in copy pasting an answer. More Complete answer offer useful context. – Troy Oct 20 '22 at 23:35
  • First, you are touching on a different pattern. Namely ```Insert TableName Select * From OtherTableName``` where you do not specify the columns and use Select *. That pattern has no parenthesis next to the Insert clause nor obviously enumerated columns. Second, the very first sentence of my answer states that you must enumerate every column. Lastly, in almost every DBMS, the keyword "INTO" is optional and frequently left off. – Thomas Oct 21 '22 at 22:17
0

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.

Evan M
  • 2,573
  • 1
  • 31
  • 36