articles

Home / DeveloperSection / Articles / Copying, Merging and/or Uniting Records In SQL Server

Copying, Merging and/or Uniting Records In SQL Server

Sachindra Singh7746 12-Feb-2011
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
CreateTable 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 Records In SQL Server

 

Table structure query of NewEmployee table:

Query
CreateTable 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 Records In SQL Server

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

Query

InsertInto NewEmployee Select*FROM Employee

Execute query as shown below:
Query

select*from NewEmployee

Output

Copying, Merging and/or Uniting Records In SQL Server

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
Createtable Vehicles
(
      CarId char(10)notnull,
      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 Records In SQL Server

Table structure query of’ Vehicles’ table:

Query
CREATETABLE UsedVehicles
(
      VehicleNumber char(10)notnull,
      YearManufactured int,
      Company varchar(30),
      Model varchar(40),
      CurrentValue money
)

Rerecords available in UsedVehicles table as shown below:

 

Copying, Merging and/or Uniting Records In SQL Server

 

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 AsTarget
Using Vehicles AsSource
ON (Target.VehicleNumber =Source.CarId)
WhenNotMatchedByTarget
    ThenInsert(VehicleNumber, YearManufactured,
       Company, Model, CurrentValue)
       Values(CarId, Year, Company, Model, Cost)
WhenMatched
ThenUpdateSetTarget.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 Records In SQL Server

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
CreateTable SingerList
(
      Singer varchar(50),
      SongName varchar(50),
      CopyrightYear int
)
 SingerList

Copying, Merging and/or Uniting Records In SQL Server

Query
CreateTable 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 Records In SQL Server

Query
select Artist as'Singer',Title as'Song', YearReleased as'Launch'   from  RockSongDetail
union select*from SingerList;

 

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

Output

Copying, Merging and/or Uniting Records In SQL Server

 

X

Updated 03-Jun-2020

Leave Comment

Comments

Liked By