Generating rows for insertion

[geon]

Hi!

I have a table I'm using as a stack to pop rows from. (The data in the stack is precomputed for efficiency and reliability.) There are a lot of duplicate rows in this table (hundreds or thousands of identical rows), but they are all inserted with a regular insert statement.


I have optimized it by using the multiple row insert syntax:

INSERT INTO TableName (Col1, Col2) VALUES
("Some value", "Another value"),
("Some value", "Another value"),
("Some value", "Another value"),
("Some value", "Another value"),
 

That helps a lot, but it's still terribly slow. (Nothe that there might be hundreds of identical rows inserted like this.)

I was thinking of mowing all this to the server insted, like this:

INSERT INTO TableName SELECT "Some Value" AS Col1, "Another value" AS Col2

But I would need some way of reliably generating n rows from the select statement. I saw an example doing it this way:

INSERT INTO TableName SELECT "Some Value" AS Col1, "Another value" AS Col2 FROM AnotherTableName LIMIT n

Where "n" would be the number of rows to insert. But what if there are no rows in the second table?

Any ideas?

(This is all going to be running on a webserver I do not have control over. Probably a FreeBSD/Linux solution with Apache/MySQL/PHP.)

[mwasif]

INSERT INTO TableName SELECT "Some Value" AS Col1, "Another value" AS Col2 FROM AnotherTableName LIMIT n

Where "n" would be the number of rows to insert. But what if there are no rows in the second table?

'n' is the number of rows to SELECT. If there are no rows, it is not going to INSERT anything.

You can use this statment to INSERT the data in pieces e.g. INSERT first 100 rows

INSERT INTO TableName SELECT "Some Value" AS Col1, "Another value" AS Col2 FROM AnotherTableName LIMIT 0,1000

Then next 1000

INSERT INTO TableName SELECT "Some Value" AS Col1, "Another value" AS Col2 FROM AnotherTableName LIMIT 1000,2000