MERGE in Sql Server

From Sql server 2012 we can merge two tables. We can map data from one table to another table. If record exists can do a update and if not insert operation can be done.

Syntax

MERGE <target_table>
USING <table_source>
ON <Search>
WHEN MATCHED THEN UPDATE SET <set_clause>
WHEN NOT MATCHED THEN INSERT VALUES (<values_list>)

Create two tables and Insert some records

CREATE TABLE TargetTable([Id] INT, [Name] NVARCHAR(50))

CREATE TABLE SourceTable([Id] INT, [Name] NVARCHAR(50))

INSERT INTO TargetTable(Id,Name)
VALUES(1, 'John'), (2, 'Mezi'), (3, 'Tom')

INSERT INTO SourceTable(Id,Name)
VALUES(1, 'JohnNew'), (4, 'Peter'), (3, 'Tom-new')

Then do Merge

MERGE TargetTable AS T
USING SourceTable AS S
ON T.Id = S.Id
WHEN MATCHED THEN UPDATE SET T.Name = S.Name
WHEN NOT MATCHED THEN INSERT VALUES (S.Id, S.Name);

Now view TargetTable Data

 

CHOOSE and IIF in Sql server

CHOOSE

• Returns a list item based on its location
• First parameter is index
• Next parameters are list

SELECT CHOOSE(2,'item1','item2' )
-- this will return item2

SELECT CHOOSE(1,'item1','item2' )
-- this will return item1

SELECT
CHOOSE(1,[StandardCost],[ListPrice])
FROM [Production].[Product] p
WHERE p.ProductID = 680

--this will return StandardCost

IIF

• Instant if
• Three parameters
  • Boolean expression
  • Return value if true
  • Return value if false

SELECT IIF([ListPrice] > 0, [ListPrice], [StandardCost]) AS 'price'
FROM [Production].[Product] p
-- if ListPrice is greater than 0 it returns Listprice. Otherwise returns StandardCost

Sub Query in Sql

What is subquery?

Subquery is  just a query inside of a query.

Why Subquery?

• Break down complex logic
• Simplify reading

Subqueries can be replaced by joins. But some times Joins can be complex. Other thing is we are not allowed to put aggregates in WHERE. But we can put it aggreagates inside subquery .

Where to Use?

We can put sub queries in  three main places in query.

• SELECT
• FROM
• WHERE

Using in SELECT

• Access informations from tables  withing select query
• Query must return a single(scalar) value

Creates a dynamic table. Useful for  breaking down queries. Query must be aliased.

Note : I have used Adventureworks 2012 database for following queries 

SELECT so.[SalesOrderID]
,so.OrderDate
,(SELECT COUNT([SalesOrderDetailID])
FROM [Sales].[SalesOrderDetail]sod
WHERE sod.SalesOrderID = so.SalesOrderID ) AS 'OrderLineCount'
FROM [Sales].[SalesOrderHeader] so

Using with FROM

SELECT so.[SalesOrderID]
,so.OrderDate
,X.OrderQty AS 'TotalItems'
FROM [Sales].[SalesOrderHeader] so
INNER JOIN (SELECT
SUM([OrderQty]) AS 'OrderQty'
, [SalesOrderID]
FROM [Sales].[SalesOrderDetail] sod
GROUP BY [SalesOrderID]) AS X
ON so.[SalesOrderID] = X.[SalesOrderID]

With WHERE

Useful for comparing values from other tables

Predicates used with subqueries

IN

• Confirm column value exist in subquery
• Similar to inner join

SELECT [AccountNumber]
FROM [Sales].[Customer] sc
WHERE sc.CustomerID IN ( SELECT DISTINCT [CustomerID] FROM [Sales].[SalesOrderHeader] soh )

EXISTS

• Returns true if subquery returns value
• Used with correlated queries

Correlated subquery

Pass outer query column into subquery

</h4>
SELECT [AccountNumber]
FROM [Sales].[Customer] sc
WHERE EXISTS ( SELECT 1
FROM [Sales].[SalesOrderHeader] soh
WHERE sc.CustomerID = soh.CustomerID )

ALL

• Compares column values to all items returned by subquery
• Subquery must return only one column

ANY or SOME

• Compare column value to any item returned by subquery
• Subquery must return only one column
• ANY and SOME are identical

SELECT [AccountNumber]
FROM [Sales].[Customer] sc
WHERE sc.CustomerID = ANY ( SELECT DISTINCT [CustomerID] FROM [Sales].[SalesOrderHeader] soh )

SELECT [AccountNumber] FROM [Sales].[Customer] sc

WHERE sc.CustomerID = SOME( SELECT DISTINCT [CustomerID] FROM [Sales].[SalesOrderHeader] soh )