Update One Table with Another Table

Sometimes we need to update the data of one table with the data from another table in SQL. It can be done in different ways. We can use stored procedure, single SQL update statement or else. This article explains how to update one table by another table in SQL query. Summary of the article:

• Table Creation
• Data Insertion
• SQL Query to Update One Table with Another Table
• Output

Suppose we have two tables (Student1 and Student2). We want to update the data of Student1 table with Student2 table.

Table Creation
At first we need to create Student1 & Student2 tables.It can be done in different ways. We can create the table in database directly or can use temporary table. For simplicity here we will use table variable. Following SQL scripts can be used to declare table variable.

DECLARE @Students1 TABLE (
 StudentID int,
 StudentName nvarchar(100),
 Addresss nvarchar(150),
 Phone nvarchar(50)
 )

DECLARE @Students2 TABLE (
 StudentID int,
 StudentName nvarchar(100),
 Addresss nvarchar(150),
 Phone nvarchar(50)
 )

The above table has only four columns (StudentID, StudentName, Addresss, Phone). You can use less or more columns.

Data Insertion
After table creation we need some sample data. So, insert some demo data into the above two tables. You can use the following SQL scripts:

INSERT INTO @Students1 VALUES(1,'Name1-1','Address1-1','Phone1-1')
INSERT INTO @Students1 VALUES(2,'Name2-1','Address2-1','Phone2-1')
INSERT INTO @Students1 VALUES(3,'Name3-1','Address3-1','Phone3-1')
INSERT INTO @Students2 VALUES(1,'Name1-2','Address1-2','Phone1-2')
INSERT INTO @Students2 VALUES(2,'Name2-2','Address2-2','Phone2-2')
INSERT INTO @Students2 VALUES(3,'Name3-2','Address3-2','Phone3-2')

If we run a normal a normal SQL query to select all the data from the two tables we will get the following result. This is for observing the current status of the tables. For that sample SQL code  is given bellow:

SELECT * FROM @Students1
SELECT * FROM @Students2

SQL Query to Update One Table with Another Table
In order to update the data of Student1 table with the data of Student2 table writes the following SQL quary and execute it:

UPDATE S1
SET     S1.StudentName = S2.StudentName,
 S1.Addresss    = S2.Addresss,
 S1.Phone       = S2.Phone
FROM @Students2 AS S2 INNER JOIN
  @Students1 AS S1 ON S2.StudentID = S1.StudentID

Here only simple SQL joins  and SQL update commands are used.

Output
If we check the current status of the table, we will get the updated data. Simple SQL select query is given bellow:

SELECT * FROM @Students1
SELECT * FROM @Students2

In this way we can update one table by another table using SQL query.