Multiple INSERT queries turned into one single query

Question

I've searched through Stackoverflow and nothing answers my question properly.

My question is how do you turn multiple INSERT queries into 1 single insert query.

More specific; https://gist.github.com/gregariousjb/e73fdf1489acbbb63651 this one. That's the query I need to understand how to make into a single one.

Sample;

INSERT INTO `creature` (`guid`, `id`, ...) 
     VALUES (1, 2843, ...);
INSERT INTO `creature` (`guid`, `id`, ...)
     VALUES (2, 7853, ...);

There's 1000 of these, that needs to be turned into a single one. I sincerely appreciate any help I can get on this.

Which database are you using? – Andomar May 05 '13 at 12:53 — Andomar, May 05 '13 at 12:53

score 5 · Answer 1 · edited May 05 '13 at 12:52

5

If you are using Sql Server try the following

Insert into table (columns..)
Values(values1,value2,...), 
    (values1,value2,...),
    (values1,value2,...),
    (values1,value2,...)

edited May 05 '13 at 12:52

Andomar

answered May 05 '13 at 12:51

Amit Rai Sharma

Bohemian · Answer 2 · 2013-05-06T01:42:19.253

3

In Mysql, do this (most popular databases have a similar syntax):

INSERT INTO mytable (col1, col2, col3, ...) VALUES
(1, 2843, 0, ...),
(2, 7853, 0, ...);

In most databases, you can do this:

INSERT INTO mytable (col1, col2, col3, ...)
SELECT 1, 2843, 0, ...
UNION ALL
SELECT 2, 7853, 0, ...;

In backward, behind-the-times databases, like Oracle, you must code this second option using the artificial single-rowed table DUAL:

INSERT INTO mytable (col1, col2, col3, ...)
SELECT 1, 2843, 0, ...
FRIM DUAL
UNION ALL
SELECT 2, 7853, 0, ...
FROM DUAL;

edited May 06 '13 at 01:42

answered May 05 '13 at 12:54

Bohemian

@Denis cryptic, but I got it :) – Bohemian May 05 '13 at 12:59
The second statement isn't exactly true. In Oracle (as of 10gR2), you'd have to do `SELECT 1, 2843, 0, ... FROM DUAL`, and in Jet SQL (aka, Access), there is no equivalent. For SQL Server, though, leaving off the FROM clause definitely works. – Cheran Shunmugavel May 06 '13 at 01:29
@cheran OK Thanks for reminding me. I've updated my answer to add this Oracle peculiarity. – Bohemian May 06 '13 at 01:45

score 2 · Answer 3 · answered May 05 '13 at 12:52

2

The closest is the shorthand version that requires the field list only once:

  INSERT INTO `creature` ( <field list> ) VALUES
    ( <value list> ),
    ( <value list> ),
    ( <value list> )

answered May 05 '13 at 12:52

Alex K.

3 Answers3