SQL Batch Insertion
-
Is there any standard SQL syntax to insert a large amount of data in to a table with one query? I'm not talking about copying data from one table to another, but creation of new rows.
I was thinking of something like:
INSERT INTO TableA
(FieldA, FieldB, FieldC)
VALUES
(x, y, z),
(x0, y0, z0),
(x1, y1, z1)
etc, etc.
Seems like it would be elegant, but nothing like that seems to exist. Or am I missing something?
-
[quote user="Kiyote"]
Is there any standard SQL syntax to insert a large amount of data in to a table with one query? I'm not talking about copying data from one table to another, but creation of new rows.
I was thinking of something like:
INSERT INTO TableA
(FieldA, FieldB, FieldC)
VALUES
(x, y, z),
(x0, y0, z0),
(x1, y1, z1)
etc, etc.
Seems like it would be elegant, but nothing like that seems to exist. Or am I missing something?
[/quote]
Oracle has such a feature, called "bulk insert". Maybe you can rewrite your statement to something like "insert into tableA select x,y,z from tableB"?
-
The BULK INSERT statement should get you going.
Note that you have to read your data from a delimited text file on the server's disk with this method, but it's a nice speedy approach, and it's reasonably flexible with the file format.
-
Not so elegant, but you can use
insert into TableA (FieldA, FieldB, ...)select x,y, ... from SomeTable
union
select x0, y0, ... from SomeTable
union ...
where you just select the fixed values that you want and SomeTable is any table you like.
-
[quote user="Kiyote"]
Is there any standard SQL syntax to insert a large amount of data in to a table with one query? I'm not talking about copying data from one table to another, but creation of new rows.
I was thinking of something like:
INSERT INTO TableA
(FieldA, FieldB, FieldC)
VALUES
(x, y, z),
(x0, y0, z0),
(x1, y1, z1)
etc, etc.
Seems like it would be elegant, but nothing like that seems to exist. Or am I missing something?
[/quote]
MySQL supports exactly that syntax, but I assume you're asking the question coming from a MySQL backround, looking for a similar syntax in ANSI SQL. In that case, I have no clue. Good luck :p
-
Actually, I honestly had no idea MySQL had this syntax. It just seems that there are SQL set operations for everything but insert. And no, to those people "select into from" kind of clauses defeat what I was originally proposing. When you're creating a new database, there is nothing to select from to insert in to another table...unless ANSI SQL supports selecting from files or something.
It just seems odd to me that you can READ, UPDATE and DELETE in a set operation, but not CREATE, only "move".
-
[quote user="Kiyote"]
Actually, I honestly had no idea MySQL had this syntax. It just seems that there are SQL set operations for everything but insert. And no, to those people "select into from" kind of clauses defeat what I was originally proposing. When you're creating a new database, there is nothing to select from to insert in to another table...unless ANSI SQL supports selecting from files or something.
It just seems odd to me that you can READ, UPDATE and DELETE in a set operation, but not CREATE, only "move".
[/quote]
Actually, you can do it as a set, using UNION ALL:
INSERT INTO [table] (column1, column2, column3)
VALUES
SELECT 'Some';, 'Data', 'Value' UNION ALL
SELECT 'Another', 'Data', 'Item' UNION ALL
SELECT 'Yet', 'More', 'Data'
-
Assuming no one posts ZOMGWTFBBQSAUCE!!1one!1one I think I rather like that unusual UNION ALL system. Now I just need to see what the performance is like when inserting a few thousand rows.
If anyone is curious, in the near-term I'm using this to cause a database rebuild for some unit testing. The actual data that needs to go in to some tables is rather dynamic (please, just accept this at face value) and we need to have lots of it in the database. Since storing a backup file and mounting it is impractical, I figured just creating the database, building the schema and populating it with data when the tests are run is probably the cleanest way to get what I need.
At the end, I just delete the database and the code is checked for all sorts of common errors that keep cropping up and the rest are none the wiser.
-
[quote user="Kiyote"]
Actually, I honestly had no idea MySQL had this syntax. It just seems that there are SQL set operations for everything but insert. And no, to those people "select into from" kind of clauses defeat what I was originally proposing. When you're creating a new database, there is nothing to select from to insert in to another table...unless ANSI SQL supports selecting from files or something.
It just seems odd to me that you can READ, UPDATE and DELETE in a set operation, but not CREATE, only "move".
[/quote]
What about using MySQL BUILT IN LOAD DATA INFILE ...
