Instead of copying a fixed number of records, you can specify a portion as a percentage. In this case, use the following formula:

INSERT TOP (Number) PERCENT [INTO] TargetTable
SELECT WhatObject(s) FROM WhatObject

The new keyword in this formula is PERCENT. You must use it to indicate that the number in the parentheses represents a percentage value. That number must be between 0.00 and 100.00 included. Here is an example:

USE Exercise;
GO
INSERT INTO Interns
VALUES(N'28440', N'Avery', N'Herbert', 13.74),
      (N'60040', N'Lynnette', N'Douglas', 17.75),
      (N'25558', N'Washington', N'Jacob', 20.15),
      (N'97531', N'Colson', N'Lois', 17.05),
      (N'24680', N'Meister', N'Victoria', 11.60);
GO
INSERT TOP (30) PERCENT INTO Employees
SELECT InternNumber, FirstName + ' ' + Lastname, Salary FROM Interns;
GO

This would produce:

Notice that the source table (the Interns table) has nine records but only three (9 / (100/30) = 9 / 3.33 = 2.7 ≈ 3 (the closest higher integer to 2.7 is 3)) record from that table were copied into the Employees table.

Consider the following two tables:

USE Exercise;
GO
CREATE TABLE Contractors
(
	ContractorCode nchar(10), Salary money,
	LastName nvarchar(20), FirstName nvarchar(20)
);
INSERT INTO Contractors
VALUES(N'86824', 12.84, N'Chance', N'Julie'),
      (N'84005', 9.52, N'Kaihibu', N'Ayinda'),
      (N'27084', 14.26, N'Gorman', N'Alex');
GO
CREATE TABLE Employees
(
	FirstName nvarchar(50),	LastName nvarchar(20),
	EmplNbr nchar(10), HourlySalary money
);
INSERT INTO Employees
VALUES(N'Ann', N'Keans', N'22684', 20.52),
      (N'Godwin', N'Harrison', N'48157', 18.75),
      (N'Timothy', N'Journ', N'82476', 21.05),
      (N'Ralph', N'Sunny', N'15007', 15.55);
GO
SELECT ALL * FROM Contractors;
GO
SELECT ALL * FROM Employees;
GO

Moving records consists of transferring them from one table, the source, to another table, the target. Neither SQL nor Transact-SQL directly supports this operation, which happens to be extremely easy. You have many options. Probably the easiest way to do this consists of copying the records from the source to the target, then deleting the same records from the source. Here is an example:

USE Exercise;
GO

INSERT INTO Employees
SELECT FirstName, LastName, ContractorCode, Salary
FROM Contractors
WHERE Contractors.Salary >= 10.00;
GO

DELETE FROM Contractors
WHERE Contractors.Salary >= 10.00;
GO

SELECT ALL * FROM Contractors;
GO
SELECT ALL * FROM Employees;
GO

Imagine you have two tables created at different times, or by different people, or for different reasons. You may have two tables that have duplicate records (the same record(s) in more than one table, for example the same employee number and same name in two tables). You may have records in different tables but some of those records share a field's value (you may have an employee A in one table and another employee B in another table but both have the same employee number with different names, perhaps when two companies merge). As an assignment, you may be asked to combine the records of those tables into one.

1. In the Query window, type the following:
   

   -- =============================================
   -- Database:	 CarDealer
   -- Date Created: Monday 07 September 2009
   -- Author:	 FunctionX
   -- =============================================
   USE master
   GO
   
   -- Drop the database if it already exists
   IF  EXISTS (
   	SELECT name 
   		FROM sys.databases 
   		WHERE name = N'CarDealer'
   )
   DROP DATABASE CarDealer
   GO
   
   CREATE DATABASE CarDealer
   GO
   
   USE CarDealer;
   GO
   
   -- =============================================
   -- Database: CarDealer
   -- Table:    NewCars
   -- =============================================
   CREATE TABLE NewCars
   (
   	CarCode nchar(20) not null,
   	CarYear int,
   	Make nvarchar(40) default N'Honda',
   	Model nvarchar(50),
   	Price money
   );
   GO
   
   INSERT INTO NewCars(CarCode, CarYear, Model, Price)
   VALUES(N'P2740442', 2010, N'Accord', 24650),
         (N'WKL72750', 2009, N'CR-V', 25445),
         (N'68471815', 2010, N'Accord', 28245),
         (N'974PP392', 2009, N'Civic', 22500),
         (N'75854H80', 2009, N'Civic Hybrid', 14675),
         (N'26RT8137', 2010, N'Insight', 22080);
   GO
   
   -- =============================================
   -- Database: CarDealer
   -- Table:    UsedVehicles
   -- =============================================
   CREATE TABLE UsedVehicles
   (
   	VehicleNumber nchar(20) not null,
   	YearManufactured int,
   	Make nvarchar(40) default N'Honda',
   	Model nvarchar(50),
   	Mileage int,
   	OriginalPrice money,
   	CurrentValue money
   );
   GO
   
   INSERT INTO UsedVehicles
   VALUES(N'984795MM', 1998, N'Ford', N'Escort', 112683, 12420, 3250),
         (N'96304807', 2006, N'Toyota', N'Corolla', 64286, 18855, 12500);
   GO
   INSERT INTO UsedVehicles(VehicleNumber, Model, OriginalPrice)
   VALUES(N'P2740442', N'Accord', 24650),
         (N'68471815', N'Accord', 28245),
         (N'75854H80', N'Civic Hybrid', 14675);
   GO

2. Press F5 to execute
3. To see the records of each table, replace the code in the Query window with:
   

   USE CarDealer;
   GO
   SELECT CarCode AS [Car Code], CarYear AS [Year],
          Make, Model, Price AS [Market Value] FROM NewCars;
   GO
   SELECT VehicleNumber AS [Vehicle #], YearManufactured AS [Year],
          Make, Model, Mileage, OriginalPrice AS [Original Value],
          CurrentValue AS [Market Value] FROM UsedVehicles;
   GO

4. Press F5 to execute

Record merging consists of inserting the records of one table, referred to as the source, into another table, referred to as the target. When performing this operation, you will have the option to:

• Insert all records from the source to the target
• Update the records that meet a criterion
• Delete some records based on a condition

 The primary formula to merge two tables is:

MERGE Table1 AS Target
USING Table2 AS Source
ON Table1.CommonField = Table2.CommonField
WHEN MATCHED Matched Options
    THEN Match Operation(s)
WHEN NOT MATCHED BY TARGET
    THEN Not Matched By Target Operation(s)
WHEN NOT MATCHED BY SOURCE
    THEN Not Matched By Source Operation(s)

You start with the MERGE operator followed by the table to which the records will be added. Here is an example:

MERGE Contractors AS Workers

You continue with the USING operator followed by the table from which the records will be retrieved. Here is an example:

MERGE Contractors AS Workers
USING Employees AS Teachers

You must specify the condition by which the records must correspond.

To merge the records, the tables must have a common column. The columns don't have to have the same name but they should be of the same type (and size). To provide this information, type ON followed by the condition. Here is an example:

MERGE Contractors AS Workers
USING Employees AS Teachers
ON Workers.ContractorCode = Teachers.EmployeeNumber

To make the statement easier to read, you can include it in parentheses.

After specifying the tables and the records corresponding conditions, you must indicate what to do if/when a record from the source table meets a record from the target table. These conditions are set in the last part of the statement.

Consider the following tables:

CREATE TABLE Contractors
(
    ContractorCode nchar(10),
    FName nvarchar(20),
    LName nvarchar(20),
    Wage decimal(6, 2)
);
GO
CREATE TABLE Employees
(
    EmployeeNumber nchar(10),
    DateHired date,
    FirstName nvarchar(20),
    LastName nvarchar(20),
    HourlySalary money,
    EmploymentStatus nvarchar(20) null
);
GO
INSERT INTO Contractors
VALUES(N'350809', N'Mary', N'Shamberg', 14.20),
      (N'286606', N'Chryssa', N'Lurie', 20.26),
      (N'415905', N'Ralph', N'Sunny', 15.55);
GO
INSERT INTO Employees
VALUES(N'286018', N'20020426', N'Julie', N'Chance', 12.84, N'Full Time'),
      (N'286606', N'19981008', N'Ayinda', N'Kaihibu', 9.52, N'Part Time'),
      (N'922620', N'20100815', N'Ann', N'Keans', 20.52, N'Full Time'),
      (N'415905', N'20061222', N'Godwin', N'Harrison', 18.75, N'Full Time'),
      (N'682470', N'20080430', N'Timothy', N'Journ', 21.05, NULL);
GO

This would produce:

When merging records, the first conditional operation is to decide what to do if two records match. To start, you would add a WHEN MATCHED statement:

MERGE Contractors AS Workers
USING Employees AS Teachers
ON (Workers.ContractorCode = Teachers.EmployeeNumber)
WHEN MATCHED

If two records of the tables match, one thing you can do is to delete the matching record. To do this, after the WHEN MATCHED expression, add a THEN DELETE statement. Here is an example:

MERGE Contractors AS Workers
USING Employees AS Teachers
ON (Workers.ContractorCode = Teachers.EmployeeNumber)
WHEN MATCHED
    THEN DELETE;
GO

SELECT ALL * FROM Employees;
GO
SELECT ALL * FROM Contractors;
GO

Imagine that you want to merge the records of both tables by inserting the employees in the Contractors table. Instead of deleting matching records, another option is to update the matched records. Notice that there are two employees who have the same employee number as two contractors. In this case, a simple solution we will use is to precede those employee numbers by 00. We can write the same statement as follows:

MERGE Contractors AS Workers
USING Employees AS Teachers
ON (Workers.ContractorCode = Teachers.EmployeeNumber)
WHEN MATCHED
    THEN UPDATE SET ContractorCode = N'00' + ContractorCode

The other operation is to specify what to do if the records don't match: Simple, we will simply merge them (the employees) with the others (the contractors). This can be done as follows:

MERGE Contractors AS Workers
USING Employees AS Teachers
ON (Workers.ContractorCode = Teachers.EmployeeNumber)
WHEN MATCHED
    THEN UPDATE SET ContractorCode = N'00' + ContractorCode
WHEN NOT MATCHED
    THEN INSERT(ContractorCode, FName, LName, Wage)
    VALUES(EmployeeNumber, FirstName, LastName, HourlySalary);
GO

We can them see the contents of both tables again to see what records each contains now:

SELECT ALL * FROM Employees;
GO
SELECT ALL * FROM Contractors;
GO

Of course, in the same way, you can merge the records of any table to those of the other table, as long as you follow the rules. For example, based on the above tables, if you want to merge the contractors with the employees, you can specify the Employees table as the target, the Contractors table as source, and their numbers as the common column to match. Then, when the numbers match, you can specify what to do. For us, once more we can simply ask the database engine to start the number with 00. For the records that don't match, we can simply add the records from the source to the targe. Here is an example:

MERGE Employees AS Teachers
USING Contractors AS Workers
ON (Workers.ContractorCode = Teachers.EmployeeNumber)
WHEN MATCHED
    THEN UPDATE SET EmployeeNumber = N'00' + ContractorCode
WHEN NOT MATCHED
    THEN INSERT(EmployeeNumber, FirstName, LastName, HourlySalary)
    VALUES(ContractorCode, FName, LName, Wage);
GO

When you write WHEN NOT MATCHED, it is assumed that you want the cases where a record of the target matches a record of the source. As a result, you can also write the expression as WHEN NOT MATCHED BY TARGET:

MERGE Employees AS Teachers
USING Contractors AS Workers
ON (Workers.ContractorCode = Teachers.EmployeeNumber)
WHEN MATCHED
    THEN UPDATE SET EmployeeNumber = N'00' + ContractorCode
WHEN NOT MATCHED BY TARGET
    THEN INSERT(EmployeeNumber, FirstName, LastName, HourlySalary)
    VALUES(ContractorCode, FName, LName, Wage);
GO

To specify what to do if a record of the source matches one from the target, add a WHEN NOT MATCHED BY SOURCE statement. In this case, you must use either a DELETE or an UPDATE statment.

• Delete the whole code in the Query window and replace it with:
  

  USE CarDealer;
  GO
  
  MERGE UsedVehicles AS Target
  USING NewCars AS Source
  ON (Target.VehicleNumber = Source.CarCode)
  WHEN NOT MATCHED BY Target
      THEN INSERT(VehicleNumber, YearManufactured,
  	        Make, Model, OriginalPrice)
  	 VALUES(CarCode, CarYear, Make, Model, Price)
  WHEN MATCHED
      THEN UPDATE SET Target.YearManufactured = Source.CarYear,
  		    Target.Make = Source.Make,
  		    Target.Model = Source.Model,
  		    Target.OriginalPrice = Source.Price
  GO

If you do a merge using the above formula, after the merge has been performed, you would not know the results) unless you run a new query on the target table. Fortunately, you can ask the database engine to immediately display a summary of what happened. To do this, after the last THEN statement, create an OUTPUT expression. The formula to follow is:

MERGE Table1 AS Target
USING Table2 AS Source
ON Table1.CommonField = Table2.CommonField
WHEN MATCHED Matched Options
    THEN Match Operation(s)
WHEN NOT MATCHED BY TARGET Not Matched By Target Options
	THEN Not Matched By Target Operation(s)
WHEN NOT MATCHED BY SOURCE Not Matched By Source Options
	THEN Not Matched By Source Operation(s)
OUTPUT $action, DELETED | INSERTED | from_table_name.*

To get a summary of the merging operation(s):

• If you are performing only one type of operation, type OUTPUT, followed by either inserted.* or deleted.*
• If you are performing different types of operations, type OUTPUT, followed by $action, followed by either inserted.* or deleted.* or both

1. To see the records of each table, add the following last line:
   

   USE CarDealer;
   GO
   
   MERGE UsedVehicles AS Target
   USING NewCars AS Source
   ON (Target.VehicleNumber = Source.CarCode)
   WHEN NOT MATCHED BY Target
   	THEN INSERT(VehicleNumber, YearManufactured,
   		    Make, Model, OriginalPrice)
   	     VALUES(CarCode, CarYear, Make, Model, Price)
   WHEN MATCHED
   	THEN UPDATE SET Target.YearManufactured = Source.CarYear,
   			Target.Make = Source.Make,
   			Target.Model = Source.Model,
   			Target.OriginalPrice = Source.Price
   OUTPUT $action, inserted.*, deleted.*;
   GO

2. Press F5 to execute
   

3. To see the results on the target table, change the statement as follows:
   

   USE CarDealer;
   GO
   
   SELECT ALL * FROM UsedVehicles;
   GO

4. Press F5 to execute
    
   
5. Click inside the Query window and press Ctrl + A to select everything
6. To delete the database in this lesson, type the following:

   USE master;
   GO
   DROP DATABASE MusicCollection;
   GO
   DROP DATABASE CarDealer;
   GO

7. Close the Query window
8. When asked whether you want to save, click No
9. Close Microsoft SQL Server

1. What are the two rules to create a union of tables (Select 2)?
   1. There must be a maximum of 6 tables
   2. The number and the order of the columns must be the same
   3. The names of the tables must be the same
   4. The data types must be compatible
   5. The database must have a clustered index
2. What is the basic formula to create a union of two tables?

   1. SELECT WhatField(s) FROM OneTable
      UNION
      SELECT WhatField(s) FROM AnotherTable;

   2. WITH UNION
      SELECT WhatField(s) FROM OneTable
      ADD
      SELECT WhatField(s) FROM AnotherTable;

   3. SET UNION ON
      SELECT WhatField(s) FROM OneTable
      JOIN
      SELECT WhatField(s) FROM AnotherTable;

   4. SELECT WhatField(s) FROM OneTable
      UNION WITH
      SELECT WhatField(s) FROM AnotherTable;

   5. ON SELECT WhatField(s) FROM OneTable
      JOIN SELECT WhatField(s) FROM AnotherTable;
      UNION ALL
      

3. What types of DML operations can be performed in a MERGE statement (Select 3)?
   1. CREATE
   2. SELECT
   3. INSERT
   4. UPDATE
   5. DELETE

1. Answers
   1. Wrong Answer
   2. Right Answer
   3. Wrong Answer
   4. Right Answer
   5. Wrong Answer
2. Answers
   1. Right Answer
   2. Wrong Answer
   3. Wrong Answer
   4. Wrong Answer
   5. Wrong Answer
3. Answers
   1. Wrong Answer
   2. Wrong Answer
   3. Right Answer
   4. Right Answer
   5. Right Answer