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 of:

• Inserting all records from the source to the target
• Updating the records that meet a criterion
• Deleting 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 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)

You start with the MERGE operator followed by the table to which the records will be added. You continue with the USING operator followed by the table from which the records will be retrieved. 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.

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.

• 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
      

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