SQL INSERT Query also referred as SQL INSERT INTO is the command used for inserting data into database. SQL INSERT statement allows insertion of data into one table at a time.
SQL Insert Query
SQL INSERT statement inserts data into a table of the database. There are three ways to insert data into database using the INSERT INTO statement. Let’s try to understand all the ways we can use insert query in sql statements.
SQL Insert Examples
The three ways to use INSERT INTO statement are as mentioned below.
-
SQL Insert with Column Names
In order to insert data into a table we have to specify the columns and the corresponding values as shown below.
Syntax:
123INSERT INTO table_name (column(s)) VALUES value(s);In the syntax mentioned above the values should be in the same order as the column mentioned in the INSERT INTO statement. Otherwise it might throw error or data might go into wrong columns. Here is an example of sql insert with column names.
1234INSERT INTO Customer (FirstName, LastName, City, Country)VALUES ('John', 'Smith', 'New York', 'USA'); -
SQL Insert Query without Column Names
In order to insert data into a table without specifying the columns, it is important to provide the values in the same order as the columns are present in the table. Also, this should be used only when we want to insert data into every column of the table.
Syntax:
123INSERT INTO table_name VALUES value(s).Example:
123INSERT INTO Customer VALUES ('Amit, 'Kumar, 'Bangalore', 'India');This type of insert is error prone, specially when you add another column in the table later on. The data might get corrupted because of getting inserted into wrong columns, this type of insert query should be avoided wherever possible.
-
SQL INSERT INTO SELECT
SQL INSERT statement can be used along with the SELECT statement. This is usually used when we have to insert data from one table to other table.
Syntax:
123INSERT INTO first_table_name [column(s)] SELECT column(s) FROM second_table_name WHERE condition;Example:
123INSERT INTO Customer (FirstName, LastName, City, Country) SELECT LEFT(ContactName, CHARINDEX(' ',ContactName) - 1), SUBSTRING(ContactName, CHARINDEX(' ',ContactName) + 1, 100), City, Country FROM Supplier WHERE CompanyName="Sql"
SQL Insert Multiple Rows
There are situations when multiple record needs to be inserted in the database table. Let’s try to understand how to accomplish such requirements.
Using the below mentioned syntax we can make multiple insertion in the database table.
1 2 3 |
INSERT INTO table_name [column(s)] VALUES [value(s)], [value(s)]; |
Example:
1 2 3 |
INSERT INTO Student (StudentId,SudentName) VALUES (1, 'John'), (2, 'Steve'), (3, 'Henry'); |
In the above mentioned example multiple rows will be inserted in the Student table.
SQL Insert Date
Before I conclude SQL insert tutorial, let’s see how to insert date into tables. This is slightly tricky because there are vendor specific functions to get current date and time. Also there are vendor specific functions to parse string to date. Below queries will work for inserting date in MySQL database table.
1 2 3 4 5 6 7 8 9 10 11 |
-- insert current date insert into DateExample (name, insert_date) values ('Pankaj', now()); --auto convert string to date because of standard format insert into DateExample (name, insert_date) values ('David', '2018-01-23'); -- use STR_TO_DATE function for non-standard format insert into DateExample (name, insert_date) values ('Lisa', STR_TO_DATE('2018-01-23', '%Y-%m-%d')); |
Corresponding insert queries for Oracle database.
1 2 3 4 5 6 7 8 9 10 11 |
-- insert current date insert into DateExample (id, name, insert_date) values (1, 'Pankaj', sysdate); --auto convert string to date because of standard format insert into DateExample (id, name, insert_date) values (2, 'David', '23-JAN-18'); -- use TO_DATE function for non-standard format insert into DateExample (id, name, insert_date) values (3, 'Lisa', to_date('23-01-2018','DD-MM-YYYY')); |
That’s all for insert query in sql statements.
Reference: Oracle Documentation