Multiple INSERT queries turned into one single query [duplicate]

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
– Andomar

2013-05-05 12:53:01 +00:00
Commented May 5, 2013 at 12:53 — Andomar
– Andomar, Commented May 5, 2013 at 12:53

Andomar · Accepted Answer · 2013-05-05 12:52:42Z

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 5, 2013 at 12:52

Andomar

239k55 gold badges387 silver badges412 bronze badges

answered May 5, 2013 at 12:51

Amit Rai Sharma

4,2351 gold badge30 silver badges36 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Bohemian · Accepted Answer · 2013-05-06 01:42:19Z

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 6, 2013 at 1:42

answered May 5, 2013 at 12:54

Bohemian♦

427k103 gold badges603 silver badges750 bronze badges

3 Comments

Bohemian Over a year ago

@Denis cryptic, but I got it :)

Cheran Shunmugavel Over a year ago

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.

Bohemian Over a year ago

@cheran OK Thanks for reminding me. I've updated my answer to add this Oracle peculiarity.

Alex K. · Accepted Answer · 2013-05-05 12:52:07Z

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 5, 2013 at 12:52

Alex K.

177k32 gold badges275 silver badges298 bronze badges

Collectives™ on Stack Overflow

Multiple INSERT queries turned into one single query [duplicate]

3 Answers 3

Comments

3 Comments

Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

3 Comments

Comments

Linked

Related