Home

Microsoft SQL Server - Transact-SQL Example: ALL

   

Description

These are examples of using the ALL keyword of the SQL.

This is an example of using ALL in a UNION:

CREATE TABLE Employees
(
    EmployeeNumber nchar(9),
    FirstName nvarchar(20),
    LastName nvarchar(20),
    HourlySalary money,
    [Status] nvarchar(20) default N'Employee'
);
GO
CREATE TABLE Contractors
(
    ContractorCode nchar(7),
    Name1 nvarchar(20),
    Name2 nvarchar(20),
    Wage decimal(6, 2),
    [Type] nvarchar(20) default N'Contractor'
);
GO

INSERT INTO Employees(EmployeeNumber, FirstName, LastName, HourlySalary)
VALUES(N'2930-4708', N'John', N'Franks', 20.05),
      (N'8274-9571', N'Peter', N'Sonnens', 10.65),
      (N'6359-8079', N'Leslie', N'Aronson', 15.88);
GO
INSERT INTO Contractors(ContractorCode, Name1, Name2, Wage)
VALUES(N'350-809', N'Mary', N'Shamberg', 14.20),
      (N'286-606', N'Chryssa', N'Lurie', 20.26);
GO

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

This would produce:

Uniting ALL Records

 

Copying From a Table

atement has been executed, all records from the source table are copied to the target table:

 

Copying From Many Tables

You can use the ability to copy records to get records from two or more tables and add them to a another table.

The formula to follow is:

INSERT INTO TableName
SELECT WhatField(s) FROM OneTable
UNION [ALL]
SELECT WhatField(s) FROM AnotherTable;

Copying a Set of Records

Imagine you have an existing table filled with records. Instead of copying all records from that table into another table, you may want to copy only a specific number of records. To do this, use the following formula:

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

After the INSERT keyword, add TOP followed by parentheses. In the parentheses, enter the desired number of records. The rest of the formula follows the techniques we hase used so far. Here is an example:

USE Exercise;
GO

CREATE TABLE Interns
(
	InternNumber nchar(10),
	LastName nvarchar(20),
	FirstName nvarchar(20),
	Salary money
);
GO

INSERT INTO Interns
VALUES(N'30848', N'Politanoff', N'Jeannette', 22.04),
      (N'81094', N'Bragg', N'Salomon', 15.50),
      (N'20938', N'Verne', N'Daniel', 21.24),
      (N'11055', N'Beal', N'Sidonie', 12.85),
      (N'88813', N'Jensen', N'Nicholas', 20.46);
GO

INSERT TOP (3) INTO Employees
SELECT InternNumber, FirstName + ' ' + Lastname, Salary FROM Interns;
GO

SELECT * FROM Employees;
GO

This would produce:

Employees

Notice that only 3 records from the source table were copied.

 
 
 

Copying a Percentage of Records

 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:

Employees

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.

Moving Records

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

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

Moving Records

Merging Records

 

Introduction

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.

Practical LearningPractical Learning: Introducing Merging

  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

Car Dealer

Merging the Records

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.

Practical LearningPractical 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 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

Practical LearningPractical Learning: Outputting the Results of a Merge

  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
     
    Car Dealer
  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

Exercises

   

Lesson Summary Questions

  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
      

Answers

  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
 
 
   
 

Previous Copyright © 2009-2011 FunctionX.com Next