SQL- INSERT -MERGE (If Exists) sample

T-SQL - INSERT -MERGE (If Exists) Sample

---------------------------------------------------------------------------------
-- Adding Configuration Setting Encryption Type data
-- to the application configurations table.
---------------------------------------------------------------------------------
-- POC : TCarter

PRINT 'Adding Configuration Setting Encryption Type data.'

CREATE TABLE [#Configuration](
[ConfigurationId] [bigint]
,[KeyName] [varchar](75) NOT NULL
,[KeyValue] [varchar](75) NOT NULL
,[IsActive] [bit] NOT NULL
,[CreatedDate] [datetime] NOT NULL
)

INSERT INTO [#Configuration]
([ConfigurationId]
,[KeyName]
,[KeyValue]
,[IsActive]
,[CreatedDate])
VALUES (1
,'Encryption.EncryptionType'
,'DES'
,1
,GETDATE()
);

SET IDENTITY_INSERT [app].[Configuration] ON;

MERGE INTO [app].[Configuration] AS tp
USING [#Configuration] AS t
ON [t].[ConfigurationID] = [tp].[ConfigurationID]
WHEN MATCHED AND ([tp].[KeyName] <> [t].[KeyName]
OR [tp].[KeyValue] <> [t].[KeyValue]
OR [tp].[IsActive] <> [t].[IsActive]
)
THEN UPDATE
SET [KeyName] = [t].[KeyName]
,[KeyValue] = [t].[KeyValue]
,[IsActive] = [t].[IsActive]
WHEN NOT MATCHED
THEN INSERT (
[ConfigurationId]
,[KeyName]
,[KeyValue]
,[IsActive]
,[CreatedDate]
)
VALUES ([ConfigurationId]
,[KeyName]
,[KeyValue]
,[IsActive]
,[CreatedDate]) ;

SET IDENTITY_INSERT [app].[Configuration] OFF;

DROP TABLE [#Configuration];
---------------------------------------------------------------------------------

-- End Configuration data