INSERT, UPDATE, DELETE in a Table from Another Table in SQL

In the SQL Interview, many times asked to update or delete the records from a table based on another table. Sometimes it is asked to Insert the data from another table or Create a Cone of an existing table as well. So in this article, we will walk through all the interview questions and answers based on this topic.

Please visit our Youtube Channel for more SQL Interview Questions and Answers videos by the below link:

Watch SQL Interview Questions and Answers videos here.

Today's Interview Questions

The agenda for this article to cover the following Interview Questions in this article:

Q01. How can you create the table schema from another existing table in SQL? or How can you clone a table schema in SQL?

Q02. How can you create the table schema along with data from another table in SQL? or How can you clone a table in SQL?

Q03. How can you copy the records from a table into another table in SQL? or How can you clone a table data in SQL?

Q04. How can you insert multiple records into a table by using a single INSERT statement?

Q05. How can you insert the result of a stored procedure into a table in SQL?

Q06. What is the difference between the INSERT INTO vs SELECT INTO clause in SQL?

Q07. What is the difference between INSERT INTO SELECT vs INSERT INTO EXEC in SQL?

Q08. How can you update the records of a table from another table in SQL?

Q09. How can you delete the records of a table from another table in SQL?

Introduction

I have created a table "Employee" which contains the employee records and a stored procedure "procGetEmployees" which returns the employee records.

We will use this table and stored procedure in all demo and examples here in this article.

Creating Table Schema From an Existing Table (Cloning Schema)

Many times we require to create a normal table or temp table which contains all the columns the same as another existing table. So creating a complete CREATE TABLE script is very difficult if the table contains a large number of columns.

SQL Provide a shortcut way to do the same by using the SELECT INTO command:

The key thing here is the WHERE clause which contains a false condition. So If you remove the WHERE condition all the data is also being inserted into the new table.

Copying Data From an Existing Table (Cloning Data)

Many times we require to copy the data from an existing table into another existing table or temp table. So one way to do that is generating the INSERT SCRIPT then modifying the script to insert the data into the new table, and then, at last, executing the script on the server.

Instead of doing all the above steps, SQL provides a quick and shortcut way to do the same by using the INSERT INTO SELECT FROM command:

One advantage of using the above approach is that you can copy only the required fields. If there is any column that is not required, you can exclude those columns as well.

The second advantage is that you can also apply filters also using the WHERE clause for copying the data.

Creating New Table from an Existing Table with Data (Cloning Table)

Many times we require to copy the schema along with the data from an existing table. We need this to create the backup of an existing table. So one approach that you can think of is to create a table and then insert the data manually by the INSERT command.

But SQL provides a quick and short way to do the same thing by using the SELECT INTO FROM command.

Inserting Stored Procedure Result into Table (Cloning Stored Procedure Result)

Many times we need to insert the result of a stored procedure into a table or temp table for further manipulation or processing.

So SQL provides a quick way to do the same by using the INSERT INTO EXEC command:

Here the table in which you want to insert the stored procedure data must be created already.

Updating Table Records from Another Table

Many times we need to update the data of a table based on another table. In fact, we need this more frequently in real projects development. This is the most common interview question as well for SQL.

So what we have to do is, we have to use the INNER JOIN to achieve the needful. Please watch the INNER JOIN video here for more details.

Here I have just updated the Name field, but you can update any field of the target table with the value of the source table.

Deleting Table Records from Another Table

Many times we need to delete the data of a table based on another table as well. In fact, we need this more frequently in real projects development. This is the most common interview question as well for SQL.

So what we have to do is, we have to use the INNER JOIN to achieve the needful again

Here you can also use the WHERE clause to delete the required records only if required.

Inserting Multiple Records into a Table

Many times we need to insert multiple values at once in a table. We need this when we want to add some sample data or small records to a table. So instead of writing the complete INSERT script for each and every record, there is a shortcut way to do the same.

Live Demo

SQL Scripts

--Creating Stored Procedure
CREATE PROC procGetEmployees	
AS
BEGIN
		SELECT 
			Id,
			Name, 
			Email, 
			MobileNumber,
			EmployeeRoleId
		FROM Employee;
END
GO

-- Existing Table with Data
SELECT * FROM Employee;

-- Existing Stored Procedure
EXEC procGetEmployees;


-- Creating Table Schema From an Existing Table
SELECT *		
INTO EmployeeTableSchema
FROM Employee
WHERE 1 = 0;


-- Selecting Data from the Created Temp Table
SELECT * FROM EmployeeTableSchema;


-- Copying Records in a Table From an Existing Table
INSERT INTO EmployeeTableSchema
			(Name, Email, MobileNumber, EmployeeRoleId)
SELECT		 Name, Email, MobileNumber, EmployeeRoleId 
FROM Employee;



-- Selecting Data from the Table
SELECT * FROM EmployeeTableSchema;




-- Cloning an Existing Table
SELECT * INTO #tmpEmployeeTableClone			
FROM Employee;


-- Selecting Data from the Cloned Temp Table
SELECT * FROM #tmpEmployeeTableClone;


-- Inserting Records into a Table from Stored Procedure
INSERT INTO EmployeeTableSchema
			(
				Id,
				Name, 
				Email, 
				MobileNumber,
				EmployeeRoleId
			)
EXEC procGetEmployees;

-- Selecting Data from the Table
SELECT * FROM EmployeeTableSchema;



-- Updating Records of a Table From Another Table
SELECT * FROM EmployeeTableSchema; -- Target Table

UPDATE	targetTable
SET		targetTable.Name = sourceTable.Name + '_Updated'	
FROM	EmployeeTableSchema targetTable
JOIN	Employee sourceTable ON targetTable.Id = sourceTable.Id;

-- Selecting Data from the Target Table after Update
SELECT * FROM EmployeeTableSchema;



-- Deleting Records of a Table From Another Table
SELECT * FROM EmployeeTableSchema; -- Target Table

DELETE FROM	targetTable	
FROM	EmployeeTableSchema targetTable
JOIN	Employee sourceTable ON targetTable.Id = sourceTable.Id;

-- Selecting Data from the Target Table after Delete
SELECT * FROM EmployeeTableSchema;


-- Inserting Multiple Records into a Table
SELECT * FROM EmployeeTableSchema; 

INSERT INTO EmployeeTableSchema
			(Name, Email, MobileNumber, EmployeeRoleId)
VALUES		('James', 'james@gmail.com', '9845372890', 1),
			('Thomas', 'thomas@gmail.com', '9745372890', 2),
			('Peter', 'peter@gmail.com', '9645372890', 3),
			('Sid', 'sid@gmail.com','9445372890', 4)

-- Selecting Data from the Table
SELECT * FROM EmployeeTableSchema;

Sunday, February 27, 2022

INSERT, UPDATE, DELETE in a Table from Another Table in SQL