---
title: "Copying, Merging and/or Uniting Records In SQL Server"  
description: "Some time we have a situation to copy a set of rows of a Table 'A' into the Table ‘B’, it means suppose if we have thousand of records in a table ‘A’"  
author: "Sachindra Singh"  
published: 2011-02-12  
updated: 2020-06-03  
canonical: https://www.mindstick.com/articles/446/copying-merging-and-or-uniting-records-in-sql-server  
category: "database"  
tags: ["database"]  
reading_time: 4 minutes  

---

# Copying, Merging and/or Uniting Records In SQL Server

##### Copying Records

Some time we have a situation to copy a set of rows of a Table 'A' into the Table ‘B’, it means suppose if we have thousand of records in a table ‘A’ and I created another table ‘B’ to maintain data but records is available in table ‘A’ and I want to insert all records of table ‘A’ into table ‘B’., SQL Server provides us simple way to copy all records from specific table and can easily insert into another table as shown below:

For example: I have one table ‘Employee’ and this table have some records but I want to copy all records of ‘Employee’ table and insert into another table ‘NewEmployee’ as shown below:

##### Table structure query of Employee table:

##### Query

```
Create Table Employee(      EmployeeId char(10),      FirstName varchar(20),      LastName varchar(20),      Wage money)
```

Some records available in Employee table as shown below:

##### Records

![Copying, Merging and/or Uniting](https://www.mindstick.com/mindstickarticle/4a1b49e1-047c-4020-b194-d2ea5f2dc44a/images/66a2031e-55ee-46e1-b1aa-cc7bb82dc28a.png)

Table structure query of NewEmployee table:

##### Query

```
Create Table NewEmployee(      EmployeeId char(10),      FirstName varchar(20),      LastName varchar(20),      Salary money )
```

Some records also available in NewEmployee table as shown below:

##### Records

![Copying, Merging and/or Uniting](https://www.mindstick.com/mindstickarticle/4a1b49e1-047c-4020-b194-d2ea5f2dc44a/images/cf12482a-dc71-4e42-999d-1d5cc9dd1e81.png)

We can copy records of any table and insert into another table query as shown below:

##### Query

Insert Into NewEmployee Select * FROM Employee

##### Execute query as shown below:

##### Query

select * from NewEmployee

##### Output

![Copying, Merging and/or Uniting](https://www.mindstick.com/mindstickarticle/4a1b49e1-047c-4020-b194-d2ea5f2dc44a/images/33b5966f-ee15-4062-8471-8760e85b714b.png)

##### Merging Records

The MERGE statement basically merges data from a source table to a target table based on a condition that you specify. The new SQL command combines the sequence of conditional INSERT, UPDATE and DELETE commands in a single atomic statement, depending on the existence of a record. The new MERGE SQL command as shown below:

I have two tables ’Vehicles’ and ‘UsedVehicles’ as shown below:

Table structure query of’ Vehicles’ table:

##### Query

```
Create table Vehicles(      CarId char(10) not null,      Year int,      Company varchar(40),      Model varchar(50),      Cost money)
```

Some records available in Vehicles table as shown below:

##### Records

![Copying, Merging and/or Uniting in SQL Server](https://www.mindstick.com/mindstickarticle/4a1b49e1-047c-4020-b194-d2ea5f2dc44a/images/be80ae4e-4224-43e7-afab-de316dbc631a.png)

Table structure query of’ Vehicles’ table:

##### Query

```
CREATE TABLE UsedVehicles(      VehicleNumber char(10) not null,      YearManufactured int,      Company varchar(30),      Model varchar(40),      CurrentValue money)
```

Rerecords available in UsedVehicles table as shown below:

![Copying, Merging and/or Uniting in SQL Server](https://www.mindstick.com/mindstickarticle/4a1b49e1-047c-4020-b194-d2ea5f2dc44a/images/47c9a962-18ed-4861-9c62-22e223210117.png)

In this example I will take a UsedVehicles table as target table and Vehicles as a source table containing updated list of Vehicles and insert the records of Vehicles that does not exists in UsedVehicles table. I will then use the MERGE SQL command to synchronize the target table with the source table.

##### Query

```
Merge UsedVehicles As TargetUsing Vehicles As SourceON   (Target.VehicleNumber = Source.CarId)When Not Matched By Target     Then Insert(VehicleNumber, YearManufactured,       Company, Model, CurrentValue)       Values(CarId, Year, Company, Model, Cost)When MatchedThen Update Set Target.YearManufactured = Source.Year,                Target.Model = Source.Model,Target.CurrentValue = Source.Cost;
```

After merging the records Execute query as shown below:

##### Query

select * from UsedVehicles

##### Output

![Copying, Merging and/or Uniting in SQL Server](https://www.mindstick.com/mindstickarticle/4a1b49e1-047c-4020-b194-d2ea5f2dc44a/images/984a636a-f5df-4b68-af5d-27f5d295e4d2.png)

##### Uniting Records

Sometimes, you don’t want record to merge them , you want to display, in one view, the records of more than one table. To support the ability to select records of various tables and show them together, use the UNION operator but tables must have the same number of columns as well as same data type of columns example shown below:

##### Query

```
Create Table SingerList(      Singer varchar(50),      SongName varchar(50),      CopyrightYear int)
```

##### SingerList

![Copying, Merging and/or Uniting in SQL Server](https://www.mindstick.com/mindstickarticle/4a1b49e1-047c-4020-b194-d2ea5f2dc44a/images/78a09eef-62f4-4d41-a571-1d02ec45f5e3.png)

##### Query

```
Create Table RockSongDetail (      Artist varchar(50),      Title varchar(50) ,      YearReleased int)
```

##### Query

```
Create Table RockSongDetail (      Artist varchar(50),      Title varchar(50) ,      YearReleased int)
```

\

![Copying, Merging and/or Uniting in SQL Server](https://www.mindstick.com/mindstickarticle/4a1b49e1-047c-4020-b194-d2ea5f2dc44a/images/44dccbde-6ccf-45d7-aff6-db1741e87d56.png)

##### Query

```
select Artist as 'Singer',Title as 'Song', YearReleased as 'Launch'   from  RockSongDetailunion select * from SingerList;
```

After executed SQL query the output will be as shown below:

##### Output

![Copying, Merging and/or Uniting in SQL Server](https://www.mindstick.com/mindstickarticle/4a1b49e1-047c-4020-b194-d2ea5f2dc44a/images/da30c086-27fd-413a-88f3-32869ece275a.png)

X

---

Original Source: https://www.mindstick.com/articles/446/copying-merging-and-or-uniting-records-in-sql-server

Copyright © MindStick Software Pvt. Ltd. This Markdown version is provided for developers, AI systems, and offline reading.