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
– imran, Commented May 1, 2014 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
– Suvendu Shekhar Giri, Commented May 1, 2014 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
– Ploetzeneder, Commented May 1, 2014 at 19:59
For general single-random-rows in SQL, you'll be interested in this question. But no, there really isn't a good way to get 10 different random rows without making 10 different SELECTs. — Clockwork-Muse
– Clockwork-Muse, Commented May 4, 2014 at 0:13

Richard Christensen · Accepted Answer · 2014-05-01 19:51:49Z

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 1, 2014 at 19:51

answered May 1, 2014 at 19:46

Richard Christensen

2,18619 silver badges28 bronze badges

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

1 Comment

Ploetzeneder Over a year ago

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,...

Collectives™ on Stack Overflow

Generating test table from existing data using SQL

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related