0

I would like to be able to insert a large number of sequential records into a table with an AUTO_INCREMENT field, setting the value of another field to a constant value for all the inserted rows, using a single query.

Example:

CREATE TABLE CardNumbers (
    First15 BIGINT NOT NULL AUTO_INCREMENT,
    CheckDigit DECIMAL(1, 0) NOT NULL,
    PRIMARY KEY (First15)
) ENGINE=InnoDB AUTO_INCREMENT=123456789012345;

I would like to set the CheckDigit to 0 for all of these to start, and then calculate it later from the First15.

I imagine a query like:

INSERT INTO `CardNumbers` (CheckDigit)
SELECT 0 FROM (...) LIMIT 1000000;

Where (...) creates an infinite set of rows or similar.

mkopala
  • 1,262
  • 3
  • 12
  • 15
  • `INSERT INTO CardNumbers (CheckDigit) VALUES (0), (0), (0), (0)...., (0)` - something like this? I don't completely understand what you mean, but the snippet above will insert 0 for CheckDigit value and number of times it does that is number of (0) there are. If you need something like specifying a number for how many records you want to create, a function would be required. – N.B. Oct 22 '14 at 07:58
  • The example `INSERT` I provided was meant to insert 1 million rows. – mkopala Oct 23 '14 at 18:33
  • Well, your question isn't exactly clear. If you're wondering how to efficiently insert 1 million rows - you don't do it in 1 query. You do it in batches, using transactions and prepared statements. You prepare a statement and execute it, binding different value(s) to it. Every 1000 inserts, you commit a transaction and start a new one. That's how you make it quick and efficient. But since I don't know whether that's what you're after, I can't help further. Can you provide more details? – N.B. Oct 23 '14 at 19:31

0 Answers0