How to insert multiple data into a table using auto_increment primary key column?
So I am using SQLite 3.6 and am trying to insert into a table multiple data which has 3 columns (1 AI PK) using
INSERT INTO 'tablename'
SELECT 'data1' AS 'column1', 'data2' AS 'column2'
UNION SELECT 'data3', 'data4'
UNION SELECT 'data5', 'data6'
UNION SELECT 'data7', 'data8'
somehow it didn't work and i got this error
table transport_type has 3 columns, but 2 values were provided
Is there any other way to enter multiple data?
source to share
If you do not provide data for all columns in the table, or the data you provide is not in the same order as the columns in the table, you must provide a list of column names:
INSERT INTO tablename(column1, column2)
SELECT ...
Related examples:
-
Omitting some columns to use their default values.
This is technically the same as above, but it demonstrates the use of non-autoincrement columns.
INSERT INTO myTable(column1, column7, column21) SELECT 'one', 'seven', 'twenty-one';
-
Inserting columns in a different order than specified.
For a table with the following definition:
CREATE TABLE myTable ( column1 VARCHAR(255), column2 VARCHAR(255), column3 VARCHAR(255), column4 VARCHAR(255) );
We can specify a list of columns to insert rows where the data is output out of order. Note that the columns are listed in a different order than the definition.
INSERT INTO myTable(column3, column4, column2, column1) SELECT 'three', 'four', 'two', 'one';
source to share