Geek asks and answers

Duplicate with sql

This may seem a little confusing at first. But I have a table with data, and I want to duplicate 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 the returned results, except that I want to change the data for one column (date). This will make newlines unique.

The reason I want to do this is because I want to get more data in this table, since I am collecting statistics for testing. 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
+3
source share
5 answers
insert into your_table
   select col1, col2, concat(col3, '_new') from your_table
+6
source

Cartesian Join . :)

+3
INSERT INTO TABLEDUPLICATES
SELECT * FROM the_table


SELECT * FROM TABLEDUPLICATES UNION 
SELECT * FROM the_table
+2

Assuming there is an identifier column (ID), you can generate dates (A_Date) as follows:

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

To duplicate rows (all columns) you simply can use

insert into tblname
select * from tblname

to change one column that can be changed to

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

But you need a unique value for column 3, so you need 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 this helps you

+2
source

More articles:


All Articles