This article will walk you through how to use both Insert and Insert Into statements in SQL.
How to use Insert in SQL
Insert queries are a way to insert data into a table. Let’s say we have created a table using
CREATE TABLE example_table ( name varchar(255), age int)
example_table
Name Age
Now to add some data to this table , we’ll use INSERT in the following way:
INSERT INTO example_table (column1,column2) VALUES ("Andrew",23)
example_table
NameAgeAndrew23
Even the following will work, but it’s always a good practice to specify which data is going into which column.
INSERT INTO table_name VALUES ("John", 28)
example_table
NameAgeAndrew23John28
How to use Insert Into in SQL
To insert a record in a table you use the INSERT INTO
statement.
You can do it in two ways, if you want to insert values only in some columns, you have to list their names including all mandatory columns. The syntax is:
INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);
The other way is inserting values to all columns in the table, it is not necessary to specify the columns names. The syntax is:
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
Here’s an example inserting a record in the table Person in both ways:
INSERT INTO Person
VALUES (1, ‘John Lennon’, ‘1940-10-09’, ‘M’);
And
INSERT INTO Person(Id, Name, DateOfBirth, Gender)
VALUES (1, ‘John Lennon’, ‘1940-10-09’, ‘M’);
Some SQL versions (for example, MySQL) support inserting multiple rows at once. For example:
INSERT INTO Person(Id, Name, DateOfBirth, Gender)
VALUES (1, ‘John Lennon’, ‘1940-10-09’, ‘M’), (2, ‘Paul McCartney’, ‘1942-06-18’, ‘M’),
(3, ‘George Harrison’, ‘1943-02-25’, ‘M’), (4, ‘Ringo Starr’, ‘1940-07-07’, ‘M’)
Note that the entire original query remains intact - we simple add on data rows enclosed by parentheses and separated by commas.
You can even use Insert Into in a Select Statement.
You can insert records in a table using data that are already stored in the database. This is only a copy of data and it doesn’t affect the origin table.
The INSERT INTO SELECT
statement combines INSERT INTO
and SELECT
statements and you can use any conditions you want. The syntax is:
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ...
FROM table1
WHERE condition;
Here is an example that inserts in the table Person all the male students from the table Students.
INSERT INTO Person(Id, Name, DateOfBirth, Gender)
SELECT Id, Name, DateOfBirth, Gender
FROM Students
WHERE Gender = ‘M’