Insert random data into DB using INSERT INTO SELECT

Hello all,

I need to pass data from one table to another, with previous help from this community, I get something like:

INSERT INTO newDatabase.newTable(name, description) SELECT old_name FROM oldDatabase.oldTable WHERE old_id > 2;

This works perfectly.

Now, the point is, not only to insert data from the old table but, as well, populate (is this the correct term?) specific table columns with random data.

However, this random data on those table columns, should be different on each new row created.

I believe something like this will take care of the random part:

$randomValue = mb_substr(md5(uniqid()), 0, 8);

And I was hopping that something like this could take care of the job:

INSERT INTO newDatabase.newTable(name, description) VALUES ((SELECT old_name FROM oldDatabase.oldTable WHERE old_id > 2), $randomValue;
  1. I’m getting a query error: “1242 - Subquery returns more then 1 row”.

  2. I’m not aware of the php logic that will allow me to have different randomValue values, each time new rows are inserted.

Can I have your help please?

Thanks in advance,

um, no it doesn’t :slight_smile:

the INSERT column list contains two columns (name,description), whereas the SELECT contains only one (old_name)

to insert random data, you might consider using the UUID mysql function

  INTO newDatabase.newTable
     ( name
     , description ) 
SELECT old_name 
     , UUID() /* and optionally some functions on this */
  FROM oldDatabase.oldTable 
 WHERE old_id > 2


You are right. I was editing the code and I end you with incorrect syntax.

I was just reading about it on gooracle. :slight_smile: However, the destination table column as some length limitations, hence, I need a max of 8 length string. (because that value will be used on the URL I suppose).

Could this be done with the optionally functions you were talking about?

One of the random fields accept only digits of 6 length size, can the UUID generate only digits as well ?
EDIT - Yes, by using UUID_SHORT(), however, it’s not 6 digits length. :frowning:

Additional Notes: I only need to do this for migration proposes, for 5.000 or so, records. Once this is done, the new records will be inserted using the system that, I suppose, has is own ways to avoid duplicates. (I cannot see, neither edit, the php new system files).

Thanks again,

with some finagling, sure

try MID(UUID(),3,6) for a 6-byte string

for a 6-digit number, try FLOOR(RAND()*1000000)

keep experimenting, keep testing


I will. I’m being introduced to mySQL functions, and the subsequent power, right now, with your post. I thought the joins would be the hard we can get. :blush: mySQL is really powerfull.

I will give it a try.

Thanks a lot,

Hello again,

I’m struggling with the possibilities again. :frowning:

On the old table we have some int values that, on the new table should correspond a specific string value. Is there a way to make this correspondence on the query already in use?

If we have something like this, it will not work as expected, because the WHERE clause is not column specific, right?

INSERT INTO newDatabase.newTable(name, status)
SELECT old_name, 'specific_string_status'
FROM oldDatabase.oldTable
WHERE old_status = 2

I can do the insert, with the old values to this new table, and then, make an UPDATE WHERE clause to convert the numeric values to string ones but, I was just wondering if this can be done with one INSERT at once.

thanks in advance,

i don’t understand this part

the WHERE clause is very specific – old_status must equal 2

He wants to know how to replace 2 with a string (without using the where). ie, case 2 string “two”, case 3 “three”.
I’d have to google tbh, not something I do daily.

ah, i get it (just a bit slow this morning)

okay, all you need is a table of statuses and their translations

you can add this table to the query “on the fly” –

  INTO newDatabase.newTable
     ( name
     , status )
SELECT t.old_name
     , x.status_string
  FROM oldDatabase.oldTable AS t
  JOIN ( SELECT 1                     AS status
              , 'string for status 1' AS status_string
         UNION ALL
         SELECT 2
              , 'string for status 2'
         UNION ALL
         SELECT 3
              , 'string for status 3'
       ) AS x
    ON x.status = t.old_status

And you say you were slow this morning hm?! Oh well… (:

I’m trying to understand this query for the last hours, lets see:

  INTO newDatabase.newTable
     ( name
     , status )
SELECT t.old_name
     , x.status_string 
FROM oldDatabase.oldTable AS t

We are saying: Insert into the newTable columns ‘name’ and ‘status’, the result of the SELECT instruction that will grab the oldTable column ‘old_name’ and the x (temporary table alias?) status_string (temporary column of the temporary table created) ?

When do we actually create that table? When we do the INNER JOIN with the AS clause?


What are the numbers on SELECT 1 SELECT 2 here:

SELECT 1 AS status, 'string for status 1' AS status_string


SELECT 2, 'string for status 2'


SELECT 3, 'string for status 3')

I was expecting that instead of 1 we had a column name.

Can I have a push on your query suggestion please?


i think you have understood my query very well :slight_smile:

just run the subquery by itself and you will see that it creates a result consisting of two columns, where the first column name is status and the second column name is status_string…

… exactly as if we had created a table for this purpose!

this use of a subquery in the FROM clause is called a derived table

the derived table can be used in the outer query exactly as if it were a “real” table – it ~is~ real, it just isn’t permanent, it exists only for the duration of the query

note that the column names for the derived table are assigned in the first SELECT of the UNION

actually, yes we are

that query returns one row consisting of one unnamed column

of course, the column ~does~ have a name (every column has a name), and i believe the default name, since you did not assign one, is 1 + 1


I will search about it, for sure. thanks.

Clear. Ok.

So that’s why the AS exists only on the first Union statement, as the mysql documentation suggests as well.

But what about the numbers? SELECT 1, SELECT 2?

I have test the subquery only, and I’ve changed the values from select 2 to select 4, and I realize that the 1, 2 etc… are actually values on our temp table columns with the alias of status (declared on the first Select of the UNION).

If we change SELECT 1 to SELECT ‘hello’ it will put this string as a value of the column status.

But I’m not understating, how/what is this 1 in the following context, is not a column, is not a row, it’s a value, but after a SELECT keyword? How?

In MySQL website, I can read:

SELECT can also be used to retrieve rows computed without reference to any table.

and the example:

mysql> SELECT 1 + 1;
        -> 2

but we are not talking about rows here, are we?

Thanks again,

by the way, other database systems have different ways of assigning column names to expressions

for example, Expr1001, Expr1002…

With the value of 2 .

So, if we run a query like the above:

SELECT 1 AS status, 'string for status 1' AS status_string
SELECT 2, 'string for status 2'
SELECT 3, 'string for status 3'

I can see on the output window of Workbench the following structure:
----1------string for status 1
----2------string for status 2
----3------string for status 3

So, can we say, about the 1 that appears after the status of this graphical representation of the resultset that:
1 is a value of this temporary result set returned, on the temporary column status at row 1 ?

2 is a value of this temporary result set returned, on the temporary column status at row 2 ?


All together now:

INSERT INTO gwebhspt_gestaowhmcs.tbltickets(id, did, tid, c, userid, message,  date, title, urgency, flag, clientunread, adminunread, replyingadmin, status)
SELECT alp.ticket_id, alp.topic_id, FLOOR(RAND()*1000000), MID(UUID(),1,8), alp.cust_id, alp.ticket_text, alp.ticket_date, alp.ticket_subject, 'Medium', 0, 0, 1, 0, x.status_string
FROM gwebhspt_testesmm.support_topics AS alp
            SELECT 0 AS status, 'Answered' AS status_string
            UNION ALL
            SELECT 1, 'Customer-Reply'
            UNION ALL
            SELECT 3, 'Closed')
       AS tmpApl
ON tmpApl.status = alp.ticket_status

I’m getting a “Unknow column in field list” - I have checked and rechecked, and is there. If I start removing just to see if the error stops occuring no matter what, I end of with the same message for x.status_string.

Any advice please?

Best Regards,

in the outer SELECT clause, you refer to x.status_string, but there is no table called x in your query

there is, however, a table called tmpApl


Thanks for checking it.

The error was still there, but after 1 hour searching how can I get a command line version of the SHOW CREATE TABLE using Workbench (that actually I was unable to found), and then been able to see the lines of SHOW CREATE TABLE using mySQL Query Browser (deprecated by the above), I end up re-looking for both tables and one of the tables names on the query was not correct.

I will see the query consequences now, but it works, I’ve learn a little bit more, and I’m waiting for a “Simple SQL” to give me some lights on SELECT 1.

Best regards, and really, thanks for your time and guidance,