|
Copying the records consists of transferring them from one
table, the source, to another table, the target. You can copy all records from
the source to the target. You can select what columns to copy. Or you can
specify under what conditions some records would be copied.
To copy all records from one table to another, the source
and the target must have the same number of columns and the same sequence of
columns with regards to their data types. To copy the records, Start an INSERT
or INSERT INTO statement. Instead of the VALUES keyword, create a
SELECT statement that involves the source table. Here is an example:
USE Exercise;
GO
INSERT INTO Employees SELECT * FROM Seasonals;
GO
Once this statement has been executed, all records from the
source table are copied to the target table:
If you use the above formula, the records of a column from
the source table would be copied to the corresponding column of the target table. Sometimes, you will want to merge tables that neither share the same sequence of
columns nor have the same number of columns. Consider the following tables:
USE Exercise;
GO
CREATE TABLE Seasonals
(
ContractorCode nchar(10),
Wage money,
LastName nvarchar(20),
FirstName nvarchar(20)
);
INSERT INTO Seasonals
VALUES(N'86824', 12.84, N'Chance', N'Julie'),
(N'84005', 9.52, N'Kaihibu', N'Ayinda');
GO
CREATE TABLE Employees
(
EmplNbr nchar(10),
EmployeeName nvarchar(50),
HourlySalary money
);
INSERT INTO Employees
VALUES(N'22684', N'Ann Keans', 20.52),
(N'48157', N'Godwin Harrison', 18.75),
(N'82476', N'Timothy Journ', 18.75),
(N'15007', N'Ralph Sunny', 18.75);
GO
In such a case, before copying the records, you must analyze
the table to figure out a way to converge the records. Here is an example:
USE Exercise1;
GO
INSERT INTO Employees
SELECT ContractorCode, FirstName + ' ' + Lastname, Wage FROM Seasonals;
GO
In the same way, you can set a condition to follow when
copying the records.
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 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.
|
 Practical
Learning: Introducing Merging
|
|
- 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),
Model nvarchar(50),
Price money
);
GO
INSERT INTO NewCars
VALUES(N'P2740442', 2010, N'Honda', N'Accord', 24650),
(N'WKL72750', 2009, N'Honda', N'CR-V', 25445),
(N'68471815', 2010, N'Honda', N'Accord', 28245),
(N'974PP392', 2009, N'Honda', N'Civic', 22500),
(N'75854H80', 2009, N'Honda', N'Civic Hybrid', 14675),
(N'26RT8137', 2010, N'Honda', N'Insight', 22080);
GO
-- =============================================
-- Database: CarDealer
-- Table: UsedVehicles
-- =============================================
CREATE TABLE UsedVehicles
(
VehicleNumber nchar(20) not null,
YearManufactured int,
Make nvarchar(40),
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, Make, Model, OriginalPrice)
VALUES(N'P2740442', N'Honda', N'Accord', 24650),
(N'68471815', N'Honda', N'Accord', 28245),
(N'75854H80', N'Honda', N'Civic Hybrid', 14675);
GO
|
- Press F5 to execute
- 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
|
- 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, each of 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 condition meets a record from the target table.
|
Practical
Learning: Merging the Records
|
|
- 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
|
|
Outputting the Results of a Merge |
|
If you do a merge using the above formula, after the merge
has been performed, you would not know the result9s) 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.
|
Practical
Learning: Outputting the Results of a Merge
|
|
- 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
|
- Press F5 to execute
- To see the results on the target table, change the statement as follows:
USE CarDealer;
GO
SELECT ALL * FROM UsedVehicles;
GO
|
- Press F5 to execute
|
Common Table Expressions (CTE)
|
|
A common table expression, or CTE, is a temporary
selection or other query operation of records from one or more tables. You use
it to get an idea of what the query operation would produce if performed on a table.
You can generate a CTE to create, select, merge, or delete records.
There are two types of common table expressions: recursive and non-recursive.
|
Practical
Learning: Introducing Common Table Expressions
|
|
- Delete everything in the Query window and replace it with the following:
USE master;
GO
IF EXISTS(SELECT name
FROM sys.databases
WHERE name = N'CeilInn3'
)
DROP DATABASE CeilInn3
GO
CREATE DATABASE CeilInn3
GO
USE CeilInn3;
GO
CREATE TABLE SleepingRooms (
RoomNumber nchar(10) not null,
RoomType nvarchar(20) default N'Bedroom',
BedType nvarchar(40) default N'Queen',
Rate money default 75.85,
Available bit default 0
);
GO
CREATE TABLE ConferenceRooms (
RoomNumber nchar(10) not null,
RoomType nvarchar(20) default N'Conference',
BedType nvarchar(40),
Rate money default 75.85,
Available bit default 0
);
GO
INSERT INTO SleepingRooms(RoomNumber, BedType, Rate, Available)
VALUES(N'104', N'Queen', 80.25, 0),
(N'105', N'King', 95.50, 1),
(N'106', N'Queen', 65.95, 1),
(N'107', N'Queen', 65.95, 1),
(N'108', N'King', 92.50, 1),
(N'109', N'Queen', 68.95, 0),
(N'110', N'Queen', 74.95, 1);
GO
INSERT INTO ConferenceRooms(RoomNumber, Rate, Available)
VALUES(N'C-120', 525.00, 1);
GO
INSERT INTO SleepingRooms(RoomNumber, RoomType, BedType, Rate, Available)
VALUES(N'116', N'Studio', N'King', 112.95, 0);
GO
INSERT INTO ConferenceRooms(RoomNumber, Rate, Available)
VALUES(N'C-122', 450.00, 1);
GO
INSERT INTO SleepingRooms(RoomNumber, RoomType, BedType, Rate, Available)
VALUES(N'202', N'Studio', N'King', 105.95, 1),
(N'203', N'Studio', N'Queen', 102.50, 1);
GO
INSERT INTO SleepingRooms(RoomNumber, BedType, Rate, Available)
VALUES(N'204', N'Double', 115.95, 1),
(N'205', N'Queen', 82.95, 0),
(N'206', N'King', 98.50, 1)
GO
INSERT INTO ConferenceRooms(RoomNumber, Rate, Available)
VALUES(N'C-302', 650.00, 1);
GO
|
- Press F5 to execute
|
Creating a Common Table Expression
|
|
The formula to create a common table expression (CTE) is: [ WITH <common_table_expression> [ ,...n ] ]
<common_table_expression>::=
expression_name [ ( column_name [ ,...n ] ) ]
AS
( CTE_query_definition )
You start with the WITH keyword followed by a name
for the temporary set. The name must be different from any table that will be
used in the CTE's expression. Later we will see the parameters you can add after
the name. After the name, type AS followed by parentheses. In the
parentheses, create a simple or composite SELECT expression. After
the code that defines the CTE, that is, after the AS(CTE_query_definition)
expression, create a SELECT statement that will produce the results.
|
Practical
Learning: Creating a Common Table Expression
|
|
- Delete everything in the Query window and replace it with:
USE CeilInn3;
GO
WITH BedRooms AS
(
SELECT * FROM SleepingRooms
)
SELECT * FROM BedRooms;
GO
|
- Press F5 to execute
- To see the results of both tables, change the statement as follows:
USE CeilInn3;
GO
WITH BedRooms AS
(
SELECT * FROM SleepingRooms
)
SELECT * FROM BedRooms;
SELECT * FROM SleepingRooms;
GO
|
- Press F5 to execute
To see a list of only available rooms from the CTE, change the
statement as follows:
USE CeilInn3;
GO
WITH BedRooms AS
(
SELECT * FROM SleepingRooms WHERE Available = 1
)
SELECT * FROM BedRooms
GO
|
Press F5 to see the result
To make sure you can externally control the results of a CTE,
you can pass a type of parameter to it. To do this, after the name of the CTE
and before the AS operator, add the parentheses and pass one or more
parameters, each represented by a name. The names of parameters must be the
exact same names of columns of the table(s) from which the CTE's statement
will be based. The number of columns must be the same as the number of columns
that will be involved in the final SELECT statement.
In the body of the CTE, use the parameter(s) as you wish.
For example, you can involve the parameter(s) in a condition in the CTE.
|
Practical
Learning: Passing Parameters to a CTE
|
|
- To pass some parameters to the CTE, delete everything in the Query
window and replace it with:
USE CeilInn3;
GO
WITH BedRooms(RoomNumber, RoomType, BedType, Rate, Available)
AS
(
SELECT RoomNumber, RoomType, BedType, Rate, Available
FROM SleepingRooms
WHERE BedType = N'Queen'
)
SELECT RoomNumber, RoomType, Rate, Available FROM BedRooms
GO
|
- Press F5 to execute
- To see a list of only available rooms from the CTE, change the
statement as follows:
USE CeilInn3;
GO
WITH BedRooms(RoomNumber, RoomType, BedType, Rate, Available)
AS
(
SELECT RoomNumber, RoomType, BedType, Rate, Available
FROM SleepingRooms
WHERE BedType = N'Queen'
)
SELECT RoomNumber, RoomType, Rate, Available
FROM BedRooms
WHERE Available = 1;
GO
|
- Press F5 to see the result
|
Recursive Common Table Expressions
|
|
A recursive common table expression is a CTE that
can contain more than one SELECT statement. In the body of the CTE, you
can create as many SELECT statements as you want but those statements
must be joined. To join them, you can use a UNION, UNION ALL, or MERGER
operator.
|
Practical
Learning: Creating a Recursive CTE
|
|
- Change the CTE's code as follows:
USE CeilInn3;
GO
WITH HotelRooms
AS
(
SELECT * FROM SleepingRooms
UNION
SELECT * FROM ConferenceRooms
)
SELECT * FROM HotelRooms;
GO
|
- Press F5 to execute
- To see a list of all available rooms, change the statement as follows:
USE CeilInn3;
GO
WITH HotelRooms
AS
(
SELECT * FROM SleepingRooms
UNION
SELECT * FROM ConferenceRooms
)
SELECT RoomNumber, RoomType, BedType, Rate
FROM HotelRooms
WHERE Available = 1;
GO
|
- Press F5 to execute
|
Non-Recursive Common Table Expressions
|
|
A non-recursive common table expression is a CTE that can be
followed by only one SELECT, INSERT, UPDATE, or DELETE
statement that involves a column from inside the CTE's body.
|
|
