There are different ways of inserting values into a database table using INSERT INTO statement, as per the requirement of the Database Administrator (DBA). So, let us discuss them one by one.

SQL INSERT INTO Statement

  • The INSERT INTO statement is used to add new data to a database.
  • The INSERT INTO statement adds a new record to a table.
  • INSERT INTO can contain values for some or all of its columns.
  • INSERT INTO can be combined with a SELECT to insert a record.
Here, we will first create a database and table into it and then, perform the INSERT INTO statement.
The following SQL statement creates a database called "Organization".
Note
Make sure you have admin privileges before creating any database. Once a database is created, you can check it in the list of databases with the following SQL command: SHOW DATABASES;
Syntax
  1. CREATE DATABASE databasename;
Example
  1. CREATE DATABASE Organization;
The following example creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName, Address, and City.
Syntax
  1. CREATE TABLE table_name (
  2. column1 datatype,
  3. column2 datatype,
  4. column3 datatype,
  5. ....
  6. );
Example
  1. CREATE TABLE Persons
  2. (
  3. PersonID int,
  4. LastName varchar(255),
  5. FirstName varchar(255),
  6. Address varchar(255),
  7. City varchar(255)
  8. );
The following SQL statement selects all the columns from the "Persons" table.
  1. select * from persons
Below is a selection from the "Persons" table in the Organization sample database.
Different Methods Of SQL Queries To Insert Data In Tables

Method 1

It is possible to write the INSERT INTO statement in different ways.
The first way specifies both the column names and the values to be inserted.
Syntax
  1. INSERT INTO table-name (column-names) VALUES (values) ;
Example
The following SQL statement inserts a new record in the "Persons" table.
  1. INSERT INTO Persons (PersonID, LastName, FirstName, Address, City)
  2. VALUES ('101', 'Erichsen', 'Tom', 'Street no-21', 'New York');
  4. INSERT INTO Persons (PersonID, LastName, FirstName, Address, City)
  5. VALUES ('102', 'Johnson', 'Marry', 'Old Street Road-43', 'California');
The selection from the "Persons" table will now look like this.
Different Methods Of SQL Queries To Insert Data In Tables

Method 2

Insert Data Only in Specified Columns.
The following SQL statement will insert a new record, but only in the "PersonID", "LastName", and "FirstName" columns.
  1. INSERT INTO Persons (PersonID, LastName,FirstName)
  2. VALUES ('103', 'Steve','Rossy')
The selection from the "Persons" table will now look like this.
Different Methods Of SQL Queries To Insert Data In Tables

Method 3

If you are adding the values for all the columns of the table, you do not need to specify the column names in the SQL query. However, make sure the order of the values is in the same order as the columns in the table. The INSERT INTO syntax would be as below.
  1. INSERT INTO Persons VALUES ('104', 'Allen', 'Ketty', 'South Side Road', 'U.S.');
Below is a selection from the "Persons" table in the Organization sample database,
  1. select * from persons
Different Methods Of SQL Queries To Insert Data In Tables

Method 4

The SQL INSERT combined with a SELECT statement
Syntax
  1. INSERT INTO table-name (column-names)
  2. SELECT column-names
  3. FROM table-name
  4. WHERE condition
Note
Here, the condition is that the number of columns and respective data types returned in a select query should match with the insert table command.
Here, we create two tables named as Customer and Customers.
Different Methods Of SQL Queries To Insert Data In Tables
The selection from the "Customers" table will now look like this.
Different Methods Of SQL Queries To Insert Data In Tables
Now, it is clear to us that the column field and datatype should be the same.
Now, create another table named as Manager with the same datatype.
  1. CREATE TABLE [dbo].[Manager](
  2. [ManagerID] [int] ,
  3. [ManagerName] [varchar](50) NULL,
  4. [ContactName] [varchar](50) NULL,
  5. [Address1] [varchar](100) NULL,
  6. [City] [varchar](50) NULL,
  7. [PostalCode] [varchar](50) NULL,
  8. [Country] [varchar](50) NULL,
  9. [salary] [int] NULL
  10. )
Different Methods Of SQL Queries To Insert Data In Tables
Insert query result into a new table,
  1. select * into CustomerCopy from Customer

The into <New Table> clause is used to copy the result set into a new table that does not exist in the database. In the above query, it creates a new table "CustomerCopy" from "Customer" table data.

The following query creates a new temporary table from the query result set.

  1. select * into #tmpCustomerCopy from Customer
Insert rows into table returned by a stored procedure

Create a stored procedure that returns data of table “Customer”.

  1. create procedure spGetCustDetails as
  2. begin
  3. select * from Customer
  4. end
Below query will insert data into table "Customer" returned by executing the procedure.
  1. insert into Customer
  2. exec spGetCustDetails

Here, the number of columns returned by the stored procedure should match with the inserting table (Customer). We can also specify the columns in the query to insert particular columns data.

Note: It is not possible to insert stored procedure result set data into a new table. Thus, create a table based on the result set returned by the stored procedure.

That's it. We have discussed all the methods of inserting data into the database table. Hope you have enjoyed reading it.