Generating test table from existing data using SQL

Question

I want to create a table of test data which is based upon existing values from my Address table.

For example I want to take a random first name value, last name value, address etc and insert this into a new row in my TestAddress table. There are around 10 columns in total.

Is there a nice way to do this without having to do one select for each column value per row?

For example

INSERT INTO TestAddress(Titel,Vorname,Nachname,Strasse,Hausnummer,Zusatz,PLZ,ORT) 

VALUES(
SELECT Titel FROM Adressen where ID = FLOOR(RAND()*50000000) ,
SELECT Vorname FROM Adressen where ID = FLOOR(RAND()*50000000)  ,
SELECT Nachname FROM Adressen where ID = FLOOR(RAND()*50000000)  ,
SELECT Strasse FROM Adressen where ID = FLOOR(RAND()*50000000) ,
SELECT Hausnummer FROM Adressen where ID = FLOOR(RAND()*50000000)  ,
SELECT Zusatz FROM Adressen where ID = FLOOR(RAND()*50000000)  ,
SELECT PLZ FROM Adressen where ID = FLOOR(RAND()*50000000) ,
SELECT ORT FROM Adressen where ID = FLOOR(RAND()*50000000)  
)

Note that the above example does not work.

sample data would be helpful. Are all inputs and the outputs from and to one single table? — imran, May 01 '14 at 19:40
A sample table with data will be useful to derive or suggest a solution to your problem. Please consider this. — Suvendu Shekhar Giri, May 01 '14 at 19:53
The solution of Richard Christensen is good, and exactly what i want to do. BUT: Is not there something better then doing 10 Selects for 10 Columns,.. — Ploetzeneder, May 01 '14 at 19:59
For general single-random-rows in SQL, [you'll be interested in this question](http://stackoverflow.com/questions/19412/how-to-request-a-random-row-in-sql). But no, there really isn't a good way to get 10 different random rows without making 10 different `SELECT`s. — Clockwork-Muse, May 04 '14 at 00:13

Richard Christensen · Accepted Answer · 2014-05-01T19:51:49.123

2

After thinking about what you are trying to ask I assume you're talking something like selecting a single column from multiple rows. and creating a new record from all of those columns.

INSERT INTO table1 (col1,col2,col3,col4,...,col10) VALUES(
  (SELECT col1 FROM table2 WHERE rowid = #) as col1,
  (SELECT col2 FROM table2 WHERE rowid = #) as col2,
  (SELECT col3 FROM table2 WHERE rowid = #) as col3,
  (SELECT col4 FROM table2 WHERE rowid = #) as col4,
  ...,
  (SELECT col10 FROM table2 WHERE rowid = #) as col10,
)

edited May 01 '14 at 19:51

answered May 01 '14 at 19:46

Richard Christensen

2,046
17
28

Hi Richard, thats exactly what i want to do but maybe with a RAND() for each of the rowids. But the performance is quite bad, if u do it like that,... – Ploetzeneder May 01 '14 at 19:58

Generating test table from existing data using SQL

1 Answers1