With insert SET syntax can I insert multiple rows?

Question

I have read in the Using The INSERT INTO ... SET Syntax In MySQL blog, that you can use the following SET syntax to insert records.

Example:

INSERT INTO mytable SET col1= 'val1', col6= 'val6', col10='val10';
INSERT INTO mytable SET col3= 'val3', col5= 'val5', col10='val10';
INSERT INTO mytable SET col4= 'val4', col5= 'val5', col6='val6';

Is it possible to insert multiple rows with only 1 statement?

or [Inserting multiple rows in mysql](https://stackoverflow.com/q/6889065/5292302) — Petter Friberg, Feb 12 '19 at 08:54
this is not duplicate question,i don't want to use another approach i.e. insert into (col_names) values (..),(..) because i have 10 columns in my table and records values (which i want to insert) are varied ie. in 1st record v1,v2, only in 2nd record v3 v4 only — ParagDineshGupta, Feb 12 '19 at 09:23
sir petter, https://www.bennadel.com/blog/2913-using-the-insert-into-set-syntax-in-mysql.htm , we can insert using 'set' also — ParagDineshGupta, Feb 12 '19 at 09:34
You need to clarify your question, that it's this exact syntax you like as you have seen people taught your question was about inserting multiple rows. — Petter Friberg, Feb 12 '19 at 09:49
I have done an edit to your question, lets see if it gets reopen, anyway the answer to your question is probably no, see https://dev.mysql.com/doc/refman/8.0/en/insert.html, there seems to be no syntaxt to add multiple rows. — Petter Friberg, Feb 12 '19 at 10:06
I assume you'd want this so that if one fails, the others aren't inserted. If so, then using a [transaction](https://dev.mysql.com/doc/refman/8.0/en/commit.html) with a rollback could solve that problem. But that MySql specific `INSERT INTO ... SET` syntax just isn't designed for multiple inserts. — LukStorms, Feb 12 '19 at 10:25

score 4 · Answer 1 · edited Feb 12 '19 at 10:22

No, the INSERT ... SET syntax cannot be used to insert multiple rows. If you look at the INSERT ... SET syntax here you'll see:

INSERT [LOW_PRIORITY | DELAYED | HIGH_PRIORITY] [IGNORE]
    [INTO] tbl_name
    [PARTITION (partition_name [, partition_name] ...)]
    SET assignment_list
    [ON DUPLICATE KEY UPDATE assignment_list]

Alongside:

assignment:
    col_name = value

assignment_list:
    assignment [, assignment] ...

Only 1 assignment_list (list of values) can be provided, hence you can only insert one row.

This is also indicated on this comment on a question asking about the differences between INSERT ... SET and INSERT ... VALUES.

score 1 · Answer 2 · answered Feb 12 '19 at 08:49

1

you can use insert into select ....

insert into tbl1 (col1, col2, col3)
   select equivalent_col1, equivalent_col2, equivalent_col3 from tbl2 
     where filterCol = 1;

The above query fetches data from tbl2 and insert into tbl1

answered Feb 12 '19 at 08:49

Derviş Kayımbaşıoğlu

28,492
4
50
72

2

simonare sir,sorry,i want to know and use insert into 'set' query,i don't want to use another approach i.e. insert into (col_names) values (..),(..) because i have 10 columns in my table and records values (which i want to insert) are varied ie. in 1st record v1,v2, only in 2nd record v3 v4 only – ParagDineshGupta Feb 12 '19 at 09:28
I couldn't understand what you mean. does @Sibert's solution works for you? – Derviş Kayımbaşıoğlu Feb 12 '19 at 11:24

sibert · Answer 3 · 2019-02-12T10:13:40.497

1

Another approach

CREATE TABLE co
    (coid int, name varchar(30));
INSERT INTO co
    (coid, name)
VALUES
    (1,'Volvo'),
    (2,'BMW'),
    (3,'Microsoft'),
    (4,'Apple')
;

You can also shorten this (exact value positions):

INSERT INTO co
VALUES
    (1,'Volvo'),
    (2,'BMW'),
    (3,'Microsoft'),
    (4,'Apple')
;

edited Feb 12 '19 at 10:13

answered Feb 12 '19 at 08:53

sibert

1,968
8
33
57

2

sibert,i am sorry,i want to know and use insert into 'set' query,i don't want to use another approach i.e. insert into (col_names) values (..),(..) because i have 10 columns in my table and records values (which i want to insert) are varied ie. in 1st record v1,v2, only in 2nd record v3 v4 only – ParagDineshGupta Feb 12 '19 at 09:26
Never heard of set. But it sounds like "UPSERT". Insert OR update values. – sibert Feb 12 '19 at 10:07

LukStorms · Answer 4 · 2019-02-12T11:01:22.683

The INSERT INTO ... SET ... syntax can't be used for multiple inserts with the same statement.

But you could use an INSERT INTO from a SELECT with UNION ALL instead.
Or an INSERT INTO from VALUES.

And then put NULL in the columns that don't have a value.

Example:

CREATE TABLE mytable
(
  id int primary key auto_increment,
  col1 varchar(30),
  col2 varchar(30),
  col3 varchar(30),
  col4 varchar(30),
  col5 varchar(30)
);

INSERT INTO mytable (col1, col2, col3, col4, col5)
SELECT 'val1' as val1, null as val2, null as val3, 'val4' as val4, null as val5
UNION ALL SELECT null, 'val2', null, null, null
UNION ALL SELECT 'val1', null, 'val3', null, 'val5';

INSERT INTO mytable (col1, col2, col3, col4, col5) VALUES 
(null, 'val2', 'val3', 'val4', null),
('val1', null, 'val3', null, 'val5');

select * from mytable;

id | col1 | col2 | col3 | col4 | col5
-: | :--- | :--- | :--- | :--- | :---
 1 | val1 | null | null | val4 | null
 2 | null | val2 | null | null | null
 3 | val1 | null | val3 | null | val5
 4 | null | val2 | val3 | val4 | null
 5 | val1 | null | val3 | null | val5

db<>fiddle here

score -1 · Answer 5 · edited Feb 12 '19 at 09:07

-1

To add multiple rows into a table using a single INSERT statement, you use the following syntax:

INSERT INTO MyTable
  ( Column1, Column2, Column3 )
VALUES
  ('John', 123, 'Lloyds Office'), 
  ('Jane', 124, 'Lloyds Office'), 
  ('Billy', 125, 'London Office'),
  ('Miranda', 126, 'Bristol Office');

edited Feb 12 '19 at 09:07

Petter Friberg

21,252
9
60
109

answered Feb 12 '19 at 08:53

Prasanth V

1
1

sir i am sorry,i want to know and use insert into 'set' query,i don't want to use another approach i.e. insert into (col_names) values (..),(..) because i have 10 columns in my table and records values (which i want to insert) are varied ie. in 1st record v1,v2, only in 2nd record v3 v4 only – ParagDineshGupta Feb 12 '19 at 09:28

With insert SET syntax can I insert multiple rows?

5 Answers5