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:
atement has been executed, all records from the source table are copied to the target table:
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;
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:
Notice that only 3 records from the source table were copied.
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.
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:
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.
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):