SQL: Insert Into
Performing inserts in
By. Jacob
Edited: 2021-02-05 23:44
To load data into a database table using a SQL insert statement may be used. The exact syntax is: insert into tbl_name () values();.
When we perform a insert on a table with a primary auto increment column, the inserted data row'(s) will automatically get a unique ID assigned. This is useful when we want to speed up select statement. A table with a unique index can help us prevent duplication of data by enabling the on duplicate key clause in our queries.
To insert data into specific columns:
insert into table_name (column1, column2, column3) values('hallo', 'tiny', 'world');
The auto increment column, if used, does not need to be included in the insert query.
Note. If a column is missing from the insert query, the default value will be used instead. Often the default is simply "null". The default value of a column can be defined when creating the table, alternatively we can also change an existing table with an alter table... query.
To insert values in the order of the columns:
insert into table_name values('data for column1', 'data for column2', 'data for column3');
Note. Examples uses lower case to increase the readability for international users, but you can use uppercase in order to highlight keywords. I.e.: INSERT INTO table_name () VALUES(). Which to use is down to personal preference.
SQL Inserts with On Duplicate Key
If you either got a unique key or a primery_key on a table, it is possible to easily prevent duplicated entries by using the on duplicate key clause. Using this clause tells your database what to do if a duplicate key is encountered. To avoid updating the data, we can just re-assign the data from a column:
insert into table_name (column1, column2, column3) values('hallo', 'tiny', 'world') on duplicate key update column1=column1;
When a duplicated key event occurs, the above query will re-assigns column1 with its own value with the result that no change is made to the data. In other words, nothing is done. Sadly there is no "on dublicate key ignore", but the above practically has the same effect, and it should also be better than "insert ignore"
Insert multiple rows with SQL
There is a few ways to insert multiple rows with a single statement, but it depends on your server version if it will work. If you are restoring a backup, you may want to consider importing the backup directly from the command line, as that will probably be easier in many cases.
The following will work in MySQL, and probably others:
insert into table_name (column1, column2, column3) values
('data for column1', 'data for column2', 'data for column3'),
('data for column1', 'data for column2', 'data for column3'),
('data for column1', 'data for column2', 'data for column3');
If you got an auto increment column, it can be left out of the statement, as it will automatically be incremented when inserting the data.
Tell us what you think: