Creating duplicate rows in the same table TSQL

Question

I am trying to create duplicates of certain rows in the same table, with different primary keys. My table has many columns, so I can't specify columns. So far, I have tried copying the rows I'm interested in into a temporary table like:

select * into #Temp from MyTable where col = value

But then in order to insert the values in #Temp back into MyTable, I'd have to

Enable Identity Insert so that I could insert all rows without specifying each of the numerous columns
Update each row in #Temp to have new keys, different from those in MyTable

Is there a more elegant solution than how I have proposed to create duplicate rows in a table?

A table as a visual example would be as follows:

MyTable (before)

|---------------------|------------------|------------------|
|      Heading 1      |     Heading 2    |     Heading 2    |
|---------------------|------------------|------------------|
|          1          |      'abc'       |      29573       |
|---------------------|------------------|------------------|
|          2          |      'bob'       |      57302       |
|---------------------|------------------|------------------|

MyTable (after)

|---------------------|------------------|------------------|
|      Heading 1      |     Heading 2    |     Heading 2    |
|---------------------|------------------|------------------|
|          1          |      'abc'       |      29573       |
|---------------------|------------------|------------------|
|          2          |      'bob'       |      57302       |
|---------------------|------------------|------------------|
|          3          |      'abc'       |      29573       |
|---------------------|------------------|------------------|
|          4          |      'bob'       |      57302       |
|---------------------|------------------|------------------|

Unfortunately, the closest answer specified columns which doesn't work for me: How can SQL create duplicate records?

Playing around with an `identity` column can be tedious. You would better enumerate the columns; even if there are many of them, that's a one-time job. — GMB, Jan 10 '20 at 22:44
Is the 2nd "heading 2" column your identity column? You want to be able to insert the same value in an identity column? Can you also share the datatype of your primary key, heading 1? — jamie, Jan 11 '20 at 00:13
You could use _dynamic SQL_ to build a statement with all of the columns explicitly named save for the identity column, then execute the statement. — HABO, Jan 11 '20 at 01:06
*Enable Identity Insert...* Why? If the PKs are not equal so why not let the identity populate for you as usual? — jean, Jan 13 '20 at 20:33

Robert Sievers · Accepted Answer · 2020-01-13T20:28:08.797

I am not sure what you mean by "can't specify columns". What prevents you from doing so? If it is just the sheer number of columns to type, and iff you are required to list all columns but one, and don't want to type it out, you can use a little known feature in SSMS. Click on the columns folder of the table in question, and drag into a new window. It will give you a comma delimited list of all columns. Remove the ID column, then select that remaining column list into a #temp table.

score 0 · Answer 2 · answered Jan 11 '20 at 03:57

I'm confused with duplicate column name, heading 2, in both before and after of MyTable. However, I assumed that you may mistyped, and assumed that the second heading 2 should be heading 3. The followings SQLs performed duplicating row by making cartesian product of MyTable and saved into #Temp.

create table #myTable (
heading_1 int,
heading_2 varchar(max),
heading_3 varchar(max)
)
insert into #myTable values(1, 'abc', '29573');
insert into #myTable values(2, 'bob', '57302');

select 
    ROW_NUMBER() OVER(ORDER BY t1.heading_2 ASC) heading_1
    ,t1.heading_2
    ,t2.heading_3 
into #Temp
from 
#myTable t1, #myTable t2

For more informations about using row_number(), visit here.

Hope this help.

Creating duplicate rows in the same table TSQL

2 Answers2