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.

  1. In the first method need to specify both the column names and the values to be inserted.
  2. In the second method need to specify only values that need to be inserted.
  3. 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.

IDName  DepartmentSalary

 Method 1:

Syntax:

Make sure the order of the values is in the same order as the columns in which you want to insert it.

Example:

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

Result:

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)

ERROR:  duplicate key value violates unique constraint “Employee_pkey”

DETAIL:  Key (ID)=(1) already exists.

So let’s add another employee data

Now following 2 records are inserted into the table.

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.

Table after insertion of the above record

 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:

Example: We will add 2 records into the table by using this syntax.

Output:

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:

Example :

We have the following table with some data inserted into it

Now, will insert the data from Master_Table columns to  Employee columns using method 3 syntax.

Output:

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:

So only one record with Serial_No as 103 will be added into Employee table.

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

 

Add a Comment

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