CODEWITHSUNDEEP
sql-servert-sql
Paradox
Paradox

Reputation: 4566

Creating duplicate rows in the same table TSQL

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

  1. Enable Identity Insert so that I could insert all rows without specifying each of the numerous columns
  2. 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?

Upvotes: 0

Views: 2701

Answers (2)

Robert Sievers
Robert Sievers

Reputation: 1353

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.

Upvotes: 1

pnbps
pnbps

Reputation: 67

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.

Upvotes: 0

Related Questions