Compound Operators



Row Constructor (or Table-Valued Constructor) as Derived Table

SELECT *
FROM
(VALUES ('USD', 'U.S. Dollar'),
('EUR', 'Euro'),
('CAD', 'Canadian Dollar'),
('JPY', 'Japanese Yen')) AS [Currency] ( [CurrencyCode], [CurrencyName] )

Multiple Value Inserts Within a Single INSERT Statement

INSERT INTO [dbo].[USState]
VALUES ('AK', 'Alaska'),
('AL', 'Alabama'),
('AR', 'Arkansas'),
('AZ', 'Arizona'),
('CA', 'California')

Table-Valued Parameters

CREATE TYPE [ContactTemplate] AS TABLE (
[Email] VARCHAR(100),
[FirstName] VARCHAR(50),
[LastName] VARCHAR(50)
)
GO

CREATE PROCEDURE [dbo].[usp_ProcessContact]
@Contact ContactTemplate READONLY
AS

-- Update First Name and Last Name for Existing Emails
UPDATE A
SET [FirstName] = B.[FirstName], [LastName] = B.[LastName]
FROM [dbo].[Contact] A INNER JOIN @Contact B ON A.[Email] = B.[Email]

-- Add New Email Addresses
INSERT INTO [dbo].[Contact] ( [Email], [FirstName], [LastName] )
SELECT [Email], [FirstName], [LastName]
FROM @Contact A
WHERE NOT EXISTS (SELECT 'X' FROM [dbo].[Contact] B
WHERE A.[Email] = B.[Email])
GO

MERGE Statement

CREATE PROCEDURE [dbo].[usp_MergeEmployee]
@EmployeeNumber VARCHAR(10),
@FirstName VARCHAR(50),
@LastName VARCHAR(50),
@Position VARCHAR(50)
AS

MERGE [dbo].[Employee] AS [Target]

USING (SELECT @EmployeeNumber, @FirstName, @LastName, @Position)
AS [Source] ( [EmployeeNumber], [FirstName], [LastName], [Position] )

ON [Target].[EmployeeNumber] = [Source].[EmployeeNumber]

WHEN MATCHED THEN

UPDATE SET [FirstName] = [Source][FirstName],
[LastName] = [Source].[LastName],
[Position] = [Source].[Position]

WHEN NOT MATCHED THEN

INSERT ( [EmployeeNumber], [FirstName], [LastName], [Position] )
VALUES ( [Source].[EmployeeNumber], [Source].[FirstName],
[Source].[LastName], [Source].[Position] );

GO

Ref : http://www.sql-server-helper.com/sql-server-2008/index.aspx