Create duplicates with sql

Question

This might sound a bit confusing at first. But I have a table with data and I want to create duplicates of all the rows in this table. So, if i do:

SELECT *
FROM the_table

it lists all the data in this table. Now, i want to create a copy of all returned results, except for that I want to change data for one column (the date). This will make the new rows unique.

The reason I want to do this is because I want more data in this table since im building statistics out of it for testing purposes. So, if I have this:

**Column1   Column2   Column3**
abc       aaa         bbb
abcd      aaaa        bbbb
abcde     aaaaa       bbbbb

The table will now contain:
**Column1   Column2   Column3**
abc       aaa         bbb
abcd      aaaa        bbbb
abcde     aaaaa       bbbbb
abc       aaa         bbb_new
abcd      aaaa        bbbb_new
abcde     aaaaa       bbbbb_new

juergen d · Accepted Answer · 2012-04-19 08:31:58Z

6

insert into your_table
   select col1, col2, concat(col3, '_new') from your_table

answered Apr 19, 2012 at 8:31

juergen d

205k40 gold badges305 silver badges377 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

John Snow Over a year ago

Thanks, I got it to work. Altho I wanted up update the current date in the reproduced lines. I used Dateadd for this purpose

Eugene · Accepted Answer · 2012-04-19 08:36:14Z

3

Consider making a Cartesian Join on your table. This will give you way more data quickly :)

answered Apr 19, 2012 at 8:36

Eugene

2,9932 gold badges34 silver badges39 bronze badges

Comments

Darren · Accepted Answer · 2012-04-19 08:31:57Z

2

INSERT INTO TABLEDUPLICATES
SELECT * FROM the_table


SELECT * FROM TABLEDUPLICATES UNION 
SELECT * FROM the_table

answered Apr 19, 2012 at 8:31

Darren

71.2k24 gold badges141 silver badges146 bronze badges

Comments

Nikola Markovinović · Accepted Answer · 2012-04-19 08:46:47Z

2

Assuming there is an identity column (ID) you might generate dates (A_Date) like this:

insert into the_table (Column1, Column2, A_Date)
select Column1, Column2, A_Date + (rand(ID) - 0.5) * 100
  from the_table

answered Apr 19, 2012 at 8:46

Nikola Markovinović

19.4k5 gold badges48 silver badges53 bronze badges

Comments

Saleh Ghaleb · Accepted Answer · 2012-04-19 08:54:35Z

2

To duplicate rows (all columns) you simply could use

insert into tblname
select * from tblname

to change one column that can be modified to

insert into tblname
select column1, column2, 'fixedvalueforcolumn3' from tblname

But you need a unique value for column 3, so you have to change 'fixedvalueforcolumn3' to a function that will generate some random (unique) value (date in your case) for column 3

insert into tblname
    select column1, column2, generateRandomValue() from tblname

Hope that will help you

answered Apr 19, 2012 at 8:54

Saleh Ghaleb

1566 bronze badges

Collectives™ on Stack Overflow

Create duplicates with sql

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

1 Comment

Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related