SQL Tutorial

SQL Tutorial SQL Introduction SQL Syntax SQL Data Types SQL OPERATORS SQL COMMANDS SQL Queries

SQL Database

SQL Create Database SQL DROP Database SQL SELECT Database

SQL Table

SQL TABLE SQL CREATE TABLE SQL COPY TABLE SQL ALTER TABLE SQL DELETE SQL TRUNCATE TABLE SQL DROP TABLE SQL UPDATE TABLE SQL INSERT TABLE

SQL SELECT

SQL SELECT Statement SQL SELECT WHERE Clause SQL SELECT IN Operator SQL BETWEEN Operator SQL SELECT BETWEEN Operator SQL SELECT AND Operator SQL SELECT OR Operator SQL SELECT LIKE Operator SQL SELECT DISTINCT SQL SELECT SUM SQL SELECT MAX SQL SELECT MIN SQL SELECT AVG

SQL Clause

SQL WHERE Clause SQL GROUP BY CLAUSE SQL ORDER BY Clause SQL HAVING Clause

SQL INSERT

SQL INSERT Statement SQL INSERT INTO Statement SQL INSERT INTO Values SQL INSERT INTO SELECT SQL Insert multiple rows

SQL JOIN

SQL JOIN SQL Inner Join SQL Left Join SQL Right Join SQL Full Join SQL CROSS Join

SQL OPERATOR

SQL Comparison SQL LOGICAL Operator SQL Cast Operator SQL Arithmetic

Difference

SQL vs NOSQL WHERE vs HAVING DELETE vs DROP GROUP BY vs ORDER BY DROP vs TRUNCATE SQL IN vs SQL EXISTS Difference between Delete, Drop and Truncate in SQL

MISC

SQL SubQuery SQL CASE Commit and Rollback in SQL Pattern Matching in SQL DDL Commands in SQL DML Commands in SQL Types of SQL Commands SQL COUNT SQL Primary Key SQL FOREIGN KEY SET Operators in SQL Check Constraint in SQL SQL EXCEPT SQL VIEW SQL WHERE Statement SQL CRUD Operation Where Condition in SQL TCL Commands in SQL Types of SQL JOINS SQL Nth Highest Salary SQL NOT OPERATOR SQL UNION ALL SQL INTERSECT SQL Data Definition Language SQL Data Manipulation Language SQL Data Control Language SQL CONSTRAINTS SQL Aggregate Operators SQL KEYS Codd’s Rules in SQL What is SQL Injection? Trigger In SQL SQL WHERE Multiple Conditions Truncate function in SQL SQL Formatter WEB SQL SQL Auto Increment Save Point in SQL space() function in SQL SQL Aggregate Functions SQL Topological Sorting SQL Injection SQL Cloning Tables SQL Aliases SQL Handling Duplicate Update Query in SQL Grant Command in SQL SQL SET Keyword SQL Order BY LIMIT SQL Order BY RANDOM

How To

How to use the BETWEEN operator in SQL How To Use INNER JOIN In SQL How to use LIKE in SQL How to use HAVING Clause in SQL How to use GROUP BY Clause in SQL How To Remove Duplicates In SQL How To Delete A Row In SQL How to add column in table in SQL ? How to drop a column in SQL? How to create a database in SQL? How to use COUNT in SQL? How to Create Temporary Table in SQL? How to Add Foreign Key in SQL? How to Add Comments in SQL? How To Use Group By Clause In SQL How To Use Having Clause In SQL How To Delete Column In Table How To Compare Date In SQL How index works in SQL How to calculate age from Date of Birth in SQL How to Rename Column name in SQL What are single row and multiple row subqueries?

SQL INSERT Table
2022-07-28 00:52:35

SQL INSERT Table

In this tutorial, we will help you to understand and learn how to add records to the table in SQL with the help of examples.

SQL INSERT query is used to add records to the table. We can add single or multiple data to a table using the INSERT query.

We can add data to the tables by mentioning the column name in the syntax or without mentioning the column name in the syntax.

We can add records to the new table and copy the existing table data into the newly created table by using the insert into select query.

There are the following two ways through which we can add data to the table:

1 SQL INSERT INTO query.

2 SQL INSERT INTO SELECT query.

Let's understand how to use the INSERT query with the help of examples.

1. SQL INSERT INTO query

SQL INSERT INTO query is used to add data to a table.

There are two ways to add data to the table by using the SQL INSERT INTO query as follows:

1 In this type, we need not mention the column's name in the syntax. We just need to mention the data to be added to the table.

2 In this type, we need to mention the specific column name in the syntax; mentioning the columns allows us to add records to the column name with their values.

1. SQL INSERT INTO query without mentioning the columns name:

The syntax of the SQL INSERT INTO query without mentioning columns name is as follows:

INSERT INTO Table_Name VALUES (Value_1, Value_2, Value_3, Value_4, Value_5, Value_6, Value_7);

We can add multiple data simultaneously into the table in the same method.

Let's take an example of this method.

Firstly, we will create a new table to add records to the table.

CREATE TABLE Player(Player_No int not null, Player_Name Varchar(100) not null, ODI_Runs int, T20_Runs int, Test_Runs int, IPL_Runs int, Best_ODI_Ranking int, Best_T20_Ranking int, Best_Test_Ranking int, Team varchar(100));

In the above query, we created the Player name table.

We will run the DESC query followed by the table name to check whether the Player table is successfully created or not:

DESC Player;

The output of the above query is as follows:

FieldType Null KeyDefaultExtra
Player_Noint(11)NO NULL 
Player_Namevarchar(100)NO NULL 
ODI_Runsint(11)YES NULL 
T20_Runsint(11)YES NULL 
Test_Runsint(11)YES NULL 
IPL_Runsint(11)YES NULL 
Best_ODI_Rankingint(11)YES NULL 
Best_T20_Rankingint(11)YES NULL 
Best_Test_Rankingint(11)YES NULL 
Teamvarchar(100)YES NULL 

Now, we will add records into the table is as follows:

INSERT INTO Player VALUES (10, ‘Sachin Tendulkar’, 18426, 10, 15921, 2334, 1, 63, 1, ‘India’);
INSERT INTO Player VALUES (19, 'Rahul Dravid', 10889, 31, 13288, 2174, 5, 0, 1, 'India');
INSERT INTO Player VALUES (14, 'Ricky Ponting', 13704, 401, 13378, 91, 1, 1, 1, 'Australia');

We added three records to the Player table. We will run the select query to check whether the data added successfully or not is as follows:

SELECT * FROM Player;

The output of the above query is as follows:

Player_NoPlayer_NameODI_RunsT20_RunsTest_RunsIPL_RunsBest_ODI_RankingBest_T20_RankingBest_Test_RankingTeam
10Sachin Tendulkar18426101592123341631India
19Rahul Dravid1088931132882174501India
14Ricky Ponting137044011337891111Australia
SQL INSERT TABLE

2. SQL INSERT INTO query with mentioning the columns name:

The syntax of the SQL INSERT INTO query with mentioning columns name is as follows:

INSERT INTO Table_Name (Column_Name1, Column_Name2, Column_Name3, Column_Name4, Column_Name5, Column_Name6, Column_Name7) VALUES (Value_1, Value_2, Value_3, Value_4, Value_5, Value_6, Value_7);

Let’s take an example on this method. 

INSERT INTO Player (Player_No, Player_Name, ODI_Runs, T20_Runs, Test_Runs, IPL_Runs, Best_ODI_Ranking, Best_T20_Ranking, Best_Test_Ranking, Team) VALUES (18, ‘Virat Kohli’, 12327, 3308, 8074, 6624, 1, 1, 1, ‘India’);
INSERT INTO Player (Player_No, Player_Name, ODI_Runs, T20_Runs, Test_Runs, IPL_Runs, Best_ODI_Ranking, Best_T20_Ranking, Best_Test_Ranking, Team) VALUES (45, ‘Rohit Sharma’, 9359, 3379, 3137, 5879, 2, 7, 5, ‘India’);
 INSERT INTO Player (Player_No, Player_Name, ODI_Runs, T20_Runs, Test_Runs, IPL_Runs, Best_ODI_Ranking, Best_T20_Ranking, Best_Test_Ranking, Team) VALUES (66, ‘Joe Root’, 6120, 893, 10458, 0, 2, 4, 1, ‘England’);
INSERT INTO Player (Player_No, Player_Name, ODI_Runs, T20_Runs, Test_Runs, IPL_Runs, Best_ODI_Ranking, Best_T20_Ranking, Best_Test_Ranking, Team) VALUES (22, ‘Kane Williamson’, 6174, 2021, 7368, 2101, 3, 3, 1, ‘New Zealand’);

We added Four records to the Player table. We will run the select query to check whether the data added successfully or not is as follows:

SELECT * FROM Player;

The output of the above query is as follows:

Player_NoPlayer_NameODI_RunsT20_RunsTest_RunsIPL_RunsBest_ODI_RankingBest_T20_RankingBest_Test_RankingTeam
10Sachin Tendulkar18426101592123341631India
19Rahul Dravid1088931132882174501India
14Ricky Ponting137044011337891111Australia
18Virat Kohli12327330880746624111India
45Rohit Sharma9359337931375879275India
66Joe Root6120893104580241England
22Kane Williamson6174202173682101331New Zealand
SQL INSERT TABLE

2. SQL INSERT INTO SELECT query

The SQL INSERT INTO SELECT query is used to add or copy data from an existing table into a newly created table. But there should be an equal number of columns and data types between the tables.

The syntax of the SQL INSERT INTO SELECT query is as follows:

INSERT INTO Table_Name_2 (SELECT * FROM Table_Name_1);

To insert into a new table from an old table, we will create a players table as shown below:

CREATE TABLE Players(Player_No int not null, Player_Name Varchar(100) not null, ODI_Runs int, T20_Runs int, Test_Runs int, IPL_Runs int, Best_ODI_Ranking int, Best_T20_Ranking int, Best_Test_Ranking int, Team varchar(100));

We will run the DESC query followed by the table name to check whether the Player table is successfully created or not:

DESC Players;

The output of the above query is as follows:

FieldType Null KeyDefaultExtra
Player_Noint(11)NO NULL 
Player_Namevarchar(100)NO NULL 
ODI_Runsint(11)YES NULL 
T20_Runsint(11)YES NULL 
Test_Runsint(11)YES NULL 
IPL_Runsint(11)YES NULL 
Best_ODI_Rankingint(11)YES NULL 
Best_T20_Rankingint(11)YES NULL 
Best_Test_Rankingint(11)YES NULL 
Teamvarchar(100)YES NULL 

We will not insert into the players table using the INSERT INTO SELECT query as shown below:

INSERT INTO Players (SELECT * FROM Player);

We will run the select query to check whether the data added successfully or not:

SELECT * FROM Players;

The output of the above query is as follows:

Player_NoPlayer_NameODI_RunsT20_RunsTest_RunsIPL_RunsBest_ODI_RankingBest_T20_RankingBest_Test_RankingTeam
10Sachin Tendulkar18426101592123341631India
19Rahul Dravid1088931132882174501India
14Ricky Ponting137044011337891111Australia
18Virat Kohli12327330880746624111India
45Rohit Sharma9359337931375879275India
66Joe Root6120893104580241England
22Kane Williamson6174202173682101331New Zealand