SQL for QA: Tutorial#3 – INSERT INTO Statement
Many times, QAs don’t find sufficient data in QA environment for testing. So they need to create some dummy data specific to their test cases. They also need to add some negative data to verify negative conditions.
For this purpose, INSERT query is used . INSERT INTO statement adds/inserts new records into a Database table.
Using 3 ways we can insert records into a database table.
- In the first method need to specify both the column names and the values to be inserted.
- In the second method need to specify only values that need to be inserted.
- Using third method we can insert the data from one table to another table.
Demo database:
created following Employee table with create table query and now will insert some data into the table by using INSERT INTO query.
| ID | Name | Department | Salary |
Method 1:
Syntax:
|
1 2 |
INSERT INTO <Table_Name> (Column1, Column2, Column3,...ColumnN) VALUES (Value1, Value2, Value3,...ValueN); |
Make sure the order of the values is in the same order as the columns in which you want to insert it.
Example:
|
1 |
INSERT INTO Employee(ID, Name, Department, Salary) Values(1,'Anna','IT', 10000); |
Note: If the data is successfully saved in the database it will give this message
INSERT 0 1
Now check the data inserted with SELECT query
|
1 |
SELECT * From Employee; |
Result:
|
1 2 3 4 5 |
ID | Name | Department | Salary ----+-------+-------------+------- 1 | Anna | IT | 10000 |
If there is an issue in INSERT query while adding a record, it displays some error message
For eg. If we try to add another employee with the same ID which is Unique key (or Primary Key)
|
1 |
INSERT INTO Employee(ID, Name, Department, Salary) Values(1,'Jenny','Testing', 30000); |
ERROR: duplicate key value violates unique constraint “Employee_pkey”
DETAIL: Key (ID)=(1) already exists.
So let’s add another employee data
|
1 |
INSERT INTO Employee(ID, Name, Department, Salary) values(2,'Jenny','Testing', 30000); |
Now following 2 records are inserted into the table.
|
1 |
SELECT * From Employee; |
|
1 2 3 4 5 6 7 |
ID | Name | Department | Salary ----+---------+-------------+------- 1 | Anna | IT | 10000 2 | Jenny | Testing | 30000 |
Using this syntax we can also insert the data in specified columns only, provided that column should allow blank / NULL values.
Example: in the following query we will insert the data only in 3 columns by skipping the ‘Department’ column.
|
1 |
INSERT INTO Employee(ID,Name,Salary) values(3,'Rosy', 30000); |
Table after insertion of the above record
|
1 2 3 4 5 6 7 8 9 |
ID | Name | Department | Salary ---+--------+-------------+------- 1 | Anna | IT | 10000 2 | Jenny | Testing | 30000 3 | Rosy | | 30000 |
Method 2:
In below syntax no need to specify the column(s) name in the SQL query if you are adding values for all the columns of the table. But make sure the order of the values is in the same order as the columns in the table.
Syntax:
|
1 |
INSERT INTO <Table_Name> VALUES (value1, value2, value3,...valueN); |
Example: We will add 2 records into the table by using this syntax.
|
1 2 3 |
INSERT INTO Employee values(4,'Robert','Testing', 60000); INSERT INTO Employee values(5,'Frank','Testing', 50000); |
Output:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 |
ID | Name | Department | Salary ---+---------+-------------+------- 1 | Anna | IT | 10000 2 | Jenny | Testing | 30000 3 | Rosy | | 30000 4 | Robert | Testing | 60000 5 | Frank | Testing | 50000 |
Note: However, it is a little risky to add data by method 2, because the data will be inserted into the wrong columns if the order of the columns changes in the table. So it is better to list the column names in the INSERT statement.
Method 3:
Hereby placing a SELECT statement within the INSERT statement, we can insert the data from one table to another.
Syntax:
|
1 2 3 4 5 6 7 |
INSERT INTO <Table_Name1> (column1, column2, ... columnN) SELECT column1, column2, ...columnN FROM <Table_Name2> [WHERE condition]; |
Example :
We have the following table with some data inserted into it
|
1 |
SELECT * FROM Master_Table; |
|
1 2 3 4 5 6 7 8 9 |
Serial_no | First_name | Last_name | City | Salary | Branch -----------+------------+------------+--------+--------+----------- 101 | Pony | Ray | Mumbai | 18000 | Admin 102 | John | Disalva | Mumbai | 40000 | Travel 103 | Sam | Danke | Pune | 42000 | HR |
Now, will insert the data from Master_Table columns to Employee columns using method 3 syntax.
|
1 2 3 4 5 |
INSERT INTO Employee(ID, Name, Department, Salary) SELECT Serial_no, First_name, Dept_name, Salary FROM Master_table; |
Output:
|
1 |
SELECT * FROM Employee; |
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
ID | Name | Department | Salary -----+---------+-------------+------- 1 | Anna | IT | 10000 2 | Jenny | Testing | 30000 3 | Rosy | | 30000 4 | Robert | Testing | 60000 5 | Frank | Testing | 50000 101 | Pony | Admin | 18000 102 | John | Travel | 40000 103 | Sam | HR | 42000 |
In this example, the last 3 records in the Employee table have been inserted using data from the Master_table table.
Note: With this method, some databases may require you to alias the column names in the SELECT to match the column names of the table you are inserting into.
So you can perform multiple inserts quickly by using this method. You can add filters to data by using the WHERE clause in SELECT query while inserting into the table.
For Example:
|
1 2 3 4 5 6 7 |
INSERT INTO Employee(ID, Name, Department, Salary) SELECT Serial_no, First_name, Dept_name, Salary FROM Master_table WHERE Serial_No=103; |
So only one record with Serial_No as 103 will be added into Employee table.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 |
ID | Name | Department | Salary -----+---------+-------- -----+------- 1 | Anna | IT | 10000 2 | Jenny | Testing | 30000 3 | Rosy | | 30000 4 | Robert | Testing | 60000 5 | Frank | Testing | 50000 103 | Sam | HR | 42000 |
Conclusion
In this article, we have briefly seen the Insert Into Statment. Please feel free to comment or suggest the enhancements to the contents. Next, we will be sharing information on the UPDATE statement. Stay Tuned!!
Tutorial Index:
- SQL For QA : Tutorial#1 – Select Query
- SQL For QA : Tutorial#2 – Where Clause
- SQL for QA: Tutorial#3 – INSERT INTO Statement
- SQL for QA: Tutorial#4 – UPDATE Statement
- SQL for QA: Tutorial#5 – DELETE Statement
- SQL for QA: Tutorial#6 – ORDER BY Clause
- SQL for QA: Tutorial#7 – GROUP BY Clause
- SQL for QA: Tutorial#8 – UNION Clause
- SQL for QA: Tutorial#9 – JOIN Clause
