Is there a shorter way to insert multiple rows in a table?

Frequently Asked Questions SQL FAQ
1

Question

In the context of this exercise, is there a shorter way to insert multiple rows in a table?

Answer

Yes, instead of inserting each row in a separate INSERT statement, you can actually insert multiple rows in a single statement.

To do this, you can list the values for each row separated by commas, following the VALUES clause of the statement.

Here is how it would look,

INSERT INTO table (col1, col2, col3)
VALUES
(row1_val1, row1_val2, row1_val3),
(row2_val1, row2_val2, row2_val3),
(row3_val1, row3_val2, row3_val3);
41 Likes
2

Can you give a 2x3 or 5x3 example? Using your 3x3 example is really unhelpful because it doesn’t tell me, the SPLite learner, which orientation the matrix is. Generally, it’s best to use rowLengths and colLengths that are different (or better, coprime) so that there is no ambiguity about whether the “rows” or “columns” are the input or output data.

For example, I don’t know which of the follow two programs are correct:

    INSERT INTO table (col1, col2) VALUES
    (3, 2),
    (Emma Stone, Daniel Radcliffe),
    (38, 29);

    INSERT INTO table (col1, col2, col3) VALUES
    (3, Emma Stone, 38),
    (2, Daniel Radcliffe, 29);

thanks
~Greg

23 Likes
3

I would say the second statement would be the correct one.

12 Likes
4

Totally the second one. I was a little confused too, mainly because of jephos249’s reference to rows in his example:

(row1_val1, row1_val2, row1_val3),
(row2_val1, row2_val2, row2_val3),
(row3_val1, row3_val2, row3_val3);

But he’s just referencing rows so that we fledgling coders know where the data will be inserted, NOT because any additional commands are necessary to place them in said row(s). SQL is smart enough to know that each string of inserted values will end up in a new row, with the values in that string populated in the corresponding columns within the row.

Your first postulation:

INSERT INTO table (col1, col2) VALUES

would likely result in an error because in the previous step we created a table with 3 columns, where this command has only provisioned for 2. Or if not an error, per se, you would end up with one column missing data.

5 Likes
5 
   INSERT INTO table (col1, col2, col3) VALUES
    (3, Emma Stone, 38),
    (2, Daniel Radcliffe, 29);

is almost good, but you have to change col1, col2, col3 by theirs real names id, name, age, and the name of the table which is here celebs

   INSERT INTO celebs (id, name, age)
   VALUES
    (3, Emma Stone, 38),
    (2, Daniel Radcliffe, 29);
18 Likes
6

Hello,
What am I writing wrong? Why doesn’t the first row appear?

image
image.png1918×1023 138 KB

2 Likes
7

Hello, @aryanwen! Welcome to the forum!

If you look at the Database Schema, you see that the celebs database only has 1 row. The row it has is the first row from your INSERT clause. To add multiple rows, you either need to do this:

INSERT INTO celebs (id, name, age) 
VALUES (1, 'Justin Bieber', 22); 

INSERT INTO celebs (id, name, age) 
VALUES (2, 'Beyonce Knowles', 33); 

INSERT INTO celebs (id, name, age) 
VALUES (3, 'Jeremy Lin', 26); 

INSERT INTO celebs (id, name, age) 
VALUES (4, 'Taylor Swift', 26);

or this:

INSERT INTO celebs (id, name, age)
VALUES 
 (1, 'Justin Bieber', 22),
 (2, 'Beyonce Knowles', 33),
 (3, 'Jeremy Lin', 26),
 (4, 'Taylor Swift', 26);

Try either method, and add:

SELECT * FROM celebs;

as the last line following your INSERT clause(s) to see the contents of celebs.

21 Likes
8

The punctuation… after each statement is just a (,) at the end really end is when you use the ( ; )
Greetings!

3 Likes
9

Just a small minor comment to your code example:

Enclose the string with quotation marks. So it should instead be:

INSERT INTO table (col1, col2, col3) VALUES 
(3, 'Emma Stone', 38), 
(2, 'Daniel Radcliffe', 29);
5 Likes
10

I dunno if that’s right but i think it depends on the start of your code
when you write the data type(INTEGER,DATE,TEXT).

11

This saves time and energy typing same query. Thanks.

1 Like
12

Capture
Capture.PNG1365×656 68.3 KB

Can someone fix it for me???

1 Like
13

Hello, @nguynquanghuy4910214.

Welcome to the forum.

If you’ll notice in your query results, you are getting the first value that you inserted. There is something at the end of your first value that is ending your INSERT INTO statement prematurely before the remaining values are inserted. :wink:

2 Likes
14

The semi colon at the end of the first line after INSERT INTO comment is ending the statement. That is why such a result is there.

15

How would you load data into a table using an external file?

16

Hello guys! when I am trying to implement current Query in Microsoft Access, it gives me an error.
Look on screen shot!

Error|690x366

18

This is a tad vague but I’ll attempt to respond. Assuming you have an SQL dump, are working on a terminal, and know the mySQL user and password credentials, while you are in your project folder inside the development environment where mySQL is installed, run the following command:

mysql -u your_username -p your_database_name < your_file.sql

19

If the data type is Text, then the value has to be into quotation mark.

20

And you have to us quotation mark for variables that are Text:

INSERT INTO table (col1, col2, col3) VALUES
    (3, 'Emma Stone', 38),
    (2, 'Daniel Radcliffe', 29);
22

I am trying to insert multiple rows using single sql statement. please tell me what is wrong in this.?

insert into employee (employee_id, first_name, last_name, gender, position, department_id, salary)
values
(2,‘abc’,‘sharma’,‘f’,‘Analyst’,100,110000),
(3,‘xyz’,‘sharma’,‘f’,‘Analyst’,100,90000),
(4,‘mno’,‘sharma’,‘m’,‘manager’,100,150000);

Error:ORA-00933: SQL command not properly ended
ORA-06512: at “SYS.WWV_DBMS_SQL_APEX_200100”, line 581
ORA-06512: at “SYS.DBMS_SYS_SQL”, line 1658
ORA-06512: at “SYS.WWV_DBMS_SQL_APEX_200100”, line 567
ORA-06512: at “APEX_200100.WWV_FLOW_DYNAMIC_EXEC”, line 2127