Insert multiple rows

Last modified: June 30, 2020 • Reading Time: 1 minutes

When you insert new records into a SQL table, typically this is done in a manner similar to what is shown below. For each new record you would have an INSERT statement, would specify the table, and then would list the new input values for that record.

INSERT INTO tablename VALUES ("value1","value2","value3",1234);
INSERT INTO tablename VALUES ("value1","value2","value3",1234);
INSERT INTO tablename VALUES ("value1","value2","value3",1234);
INSERT INTO tablename VALUES ("value1","value2","value3",1234);

However There may be times when you are inserting multiple/many rows of data and do not want to have to repeat the full INSERT INTO statement for each individual record. You can accomplish this by using slightly modified syntax.

First write a single INSERT statement and specify the field names, enclosed in parentheses, separated by commas.

Next write VALUES just once, then list the inputs for each record, enclosed in parentheses, delimited by commas.

INSERT INTO tablename
	(field1,field2,field3,field4)
VALUES 
	("value1","value2","value3",1234);
	("value1","value2","value3",1234);
	("value1","value2","value3",1234);
	("value1","value2","value3",1234);

This eliminates a lot of redundant typing, contains all the updates in a single query and can also allow for easier copy/paste of the new values into the SQL code.

Note: the VALUES clause takes a maximum of 1000 rows. If you plan to input more than 1000 records, you will need to break up your data and run INSERT INTO multiple times.

Written by: Josiah Faas
Reviewed by: Matt David

Next – List the tables in SQLite opened with ATTACH

Get new data chapters sent right to your Inbox