CODEWITHSUNDEEP
sqlsql-servert-sql
John Snow
John Snow

Reputation: 5334

Create duplicates with sql

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

Upvotes: 3

Views: 131

Answers (5)

Saleh Ghaleb
Saleh Ghaleb

Reputation: 156

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

Upvotes: 2

Nikola Markovinović
Nikola Markovinović

Reputation: 19346

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

Upvotes: 2

Eugene
Eugene

Reputation: 2985

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

Upvotes: 3

juergen d
juergen d

Reputation: 204746

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

Upvotes: 6

Darren
Darren

Reputation: 70718

INSERT INTO TABLEDUPLICATES
SELECT * FROM the_table


SELECT * FROM TABLEDUPLICATES UNION 
SELECT * FROM the_table

Upvotes: 2

Related Questions