Using the MERGE Statement to Perform an UPSERT

The term UPSERT refers to an operation that inserts rows into a table if they do not exist, otherwise they are updated.

Simple example:

USE tempdb;
GO
MERGE dbo.Product AS T  
USING dbo.NewInventory AS S 
ON T.ProductName = S.ProductName
WHEN MATCHED THEN  
  UPDATE SET T.Qty = T.Qty + S.Qty
WHEN NOT MATCHED THEN  
  INSERT (ProductName,Qty) VALUES (S.ProductName,S.Qty);
SELECT * FROM dbo.Product;

Applicable example:

MERGE example

Good article on the subject:

http://www.databasejournal.com/features/mssql/using-the-merge-statement-to-perform-an-upsert.html

Table valued parameters using User-Defined Data Types

User-Defined Data Types can be used as a reusable memory table schema.

Here is where the types are stored in a database

User-Defined Table Types

Simple example

— Create Table Type
CREATE TYPE StateTbl AS TABLE
( StateID INT
, StateCode VARCHAR(2)
, StateName VARCHAR(200)
);

— Create test procedure
CREATE PROCEDURE SortStates @S StateTbl READONLY
AS
SELECT  StateName
FROM    @s
ORDER BY StateName
RETURN 0;
GO

— Insert data and run procedure
DECLARE @p AS StateTbl
INSERT  @p
VALUES  (1, ‘NC’, ‘North Carolina’)
, (2, ‘VA’, ‘Virginia’)
, (3, ‘CO’, ‘Colorado’);

EXEC SortStates @p

Here is a video that shows how to use it https://www.youtube.com/watch?v=Ewf5bivTKdI

Encrypted MRN

USE [DB];
GO

/* Drop existing table and create new one — should be used for test only. */

IF (EXISTS (SELECT *
FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_SCHEMA = ‘dbo’
AND TABLE_NAME = ‘NewMRN’))
DROP TABLE [DB].dbo.NewMRN;
GO

CREATE TABLE NewMRN
(
MRN VARCHAR(100) ,
EncryptedMRN uniqueidentifier CONSTRAINT [DF_EncryptedMRN] DEFAULT (NEWID()) NOT NULL
CONSTRAINT PK_MRN PRIMARY KEY (MRN)
);
GO
/* Load data via Tally table */

DECLARE @max_rows AS BIGINT;
SET @max_rows = 999999;

WITH
lv0 AS (SELECT 0 AS g UNION ALL SELECT 0),
lv1 AS (SELECT 0 AS g FROM lv0 AS a CROSS JOIN lv0 AS b), — 4
lv2 AS (SELECT 0 AS g FROM lv1 AS a CROSS JOIN lv1 AS b), — 16
lv3 AS (SELECT 0 AS g FROM lv2 AS a CROSS JOIN lv2 AS b), — 256
lv4 AS (SELECT 0 AS g FROM lv3 AS a CROSS JOIN lv3 AS b), — 65,536
lv5 AS (SELECT 0 AS g FROM lv4 AS a CROSS JOIN lv4 AS b), — 4,294,967,296

Nums AS (SELECT ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS n FROM lv5)

INSERT INTO NewMRN
(MRN)
SELECT TOP (@max_rows) n FROM Nums ORDER BY n;
/* Update varchar(100) columns with preceeding zero for matching CPR+ MRN numbers */

UPDATE dbo.NewMRN — 90000 rows updated
SET MRN = ‘0’+MRN
WHERE LEN(MRN) = 5;
GO

UPDATE dbo.NewMRN — 9000 rows updated
SET MRN = ’00’+MRN
WHERE LEN(MRN) = 4;
GO

UPDATE dbo.NewMRN — 900 rows updated
SET MRN = ‘000’+MRN
WHERE LEN(MRN) = 3;
GO

UPDATE dbo.NewMRN — 90 rows updated
SET MRN = ‘0000’+MRN
WHERE LEN(MRN) = 2;
GO

UPDATE dbo.NewMRN — 9 rows updated
SET MRN = ‘00000’+MRN
WHERE LEN(MRN) = 1;
GO

–SELECT *
–FROM NewMRN;
–WHERE LEN(MRN) <> 6 — 0 rows
GO
/* Triggers to control accidental edits */

CREATE TRIGGER TR_UPD_Data ON dbo.NewMRN
AFTER UPDATE
AS
BEGIN
IF ( UPDATE (MRN) OR UPDATE (EncryptedMRN) )
BEGIN
RAISERROR (‘NO records should be modified in this table.’, 16, 1);
ROLLBACK;
END;
END;
/* Non clustered index for searching */

CREATE NONCLUSTERED INDEX [IX_EncryptpedMRN] ON [dbo].[NewMRN]
(
[EncryptedMRN] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, SORT_IN_TEMPDB = OFF, DROP_EXISTING = OFF, ONLINE = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)

GO
–SELECT [MRN]
— ,[EncryptedMRN]
— FROM [dbo].[NewMRN]
–GO