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

Copying, Merging and/or Uniting Records In SQL Server


Database Database 
Ratings:
0 Comment(s)
 5298  View(s)
Rate this:

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

 

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

 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

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

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

 

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 Target

Using Vehicles As Source

ON (Target.VehicleNumber = Source.CarId)

When Not Matched By Target

    Then Insert(VehicleNumber, YearManufactured,

       Company, Model, CurrentValue)

       Values(CarId, Year, Company, Model, Cost)

When Matched

Then 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

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

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

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 in SQL Server

 

X

Don't want to miss updates? Please click the below button!

Follow MindStick