469,572 Members | 1,306 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,572 developers. It's quick & easy.

Generating rows for insertion

geon
1
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:

Expand|Select|Wrap|Line Numbers
  1. INSERT INTO TableName (Col1, Col2) VALUES
  2. ("Some value", "Another value"),
  3. ("Some value", "Another value"),
  4. ("Some value", "Another value"),
  5. ("Some value", "Another value"),
  6.  
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:

Expand|Select|Wrap|Line Numbers
  1. 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:

Expand|Select|Wrap|Line Numbers
  1. 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.)
Oct 12 '07 #1
1 1455
mwasif
801 Expert 512MB
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO TableName SELECT "Some Value" AS Col1, "Another value" AS Col2 FROM AnotherTableName LIMIT 0,1000
Then next 1000
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO TableName SELECT "Some Value" AS Col1, "Another value" AS Col2 FROM AnotherTableName LIMIT 1000,2000
Nov 6 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

13 posts views Thread by J. Campbell | last post: by
reply views Thread by Murad Nayal | last post: by
4 posts views Thread by s99999999s2003 | last post: by
9 posts views Thread by YONETANI Tomokazu | last post: by
7 posts views Thread by Don Li | last post: by
reply views Thread by suresh191 | last post: by
4 posts views Thread by guiromero | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.