Posted: 5/10/2011 12:26:02 PM EDT
|
I'm competent with MySQL. Been using it for years especially with perl. I'm looking for a way to do a mass insert of number ranges without a mass amount of calls. I have a table with 4 columns. The columns have a default value if not set specifically during insert. I need to insert a range of numbers into one column leaving the others to default. IE - 5 to 15 into the first integer column while leaving the rest default. I could go INSERT INTO tablename values (5); INSERT INTO tablename values (6); INSERT INTO tablename values (7); INSERT INTO tablename values (8); etc.... But thats slow and painful when I have many to do... I could go INSERT INTO tablename values (5), (6), (7), (8)....; I'm not sure how that might go if I have a few million entries to do (I'm not kidding). I haven't tried that approach yet. I was hoping there was a function I could nest in there like... SELECT range(5, 15); and it would return the integers between those ranges so I could do INSERT INTO tablename values (SELECT range(5, 15)); But I haven't found a function that would do that for me. Right now I'm using a stored procedure to do an INSERT DELAYED for each value and increment through the range. AUTO_INCREMENT doesn't really help me here at all. What tricks can you supply me with? -Foxxz |
Don't know if MySQL supports common table expressions, but in T-SQL, you can at least seed your sequences with a trick like the following; though it does have recursion limits:
|
)