SQL Insert Query

Filed Under: SQL

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 query, insert query in sql

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.

  1. 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:

    
    INSERT 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.

    
    INSERT INTO Customer (FirstName, LastName, City, Country)
    VALUES ('John', 'Smith', 'New York', 'USA');
    
  2. 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:

    
    INSERT INTO table_name VALUES value(s).
    

    Example:

    
    INSERT 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.

  3. 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:

    
    INSERT INTO first_table_name [column(s)] SELECT column(s) FROM second_table_name   WHERE condition; 
    

    Example:

    
    INSERT 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.


INSERT INTO table_name [column(s)] VALUES [value(s)], [value(s)];

Example:


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.


-- 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.


-- 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

Leave a Reply

Your email address will not be published. Required fields are marked *

close
Generic selectors
Exact matches only
Search in title
Search in content
Search in posts
Search in pages