Alternative method of insert data if not found, update record if data already exists

Recover Merge SQL Statement in Microsoft SQL Server 2008 Product Overview

Previously, SQL Statement for "insert data if not found, update record if data already exists" :
If (select 1 from table where id=@id)
Update
Else
Insert

With merge function, we can do this way
MERGE Table1 cs
USING Table2 cd
on Table1.id=cd.table1_id
WHEN MATCHED THEN
UPDATE
SET cs.Cust_Name=cd.Cust_name,
cs.Cust_Surname=cd.Cust_surname,
cs.Cust_Email=cd.Cust_email
WHEN NOT MATCHED BY TARGET THEN
INSERT (Cust_ID,cust_name,cust_surname,cust_email)
VALUES(cd.Cust_ID, cd.Cust_Name,cd.Cust_Surname,cd.Cust_Email);

Merge function can do more
If not match by target
If not match by source
Output

Reference
SQL Server 2008: Locking with MERGE statement compared to Update/Insert in SQL Server 2005
SQL Server: Best way to Update row if exists, Insert if not
The MERGE Statement in SQL Server 2008
SQL SERVER – 2008 – Introduction to Merge Statement – One Statement for INSERT, UPDATE, DELETE
Using SQL Server 2008's MERGE statement

---------------------------------------------------------------------------------
Example

Create database temptest1456

use temptest1456

CREATE TABLE [dbo].[Customers](
[Cust_ID] [int] NOT NULL,
[Cust_Name] [varchar](50) NOT NULL,
[Cust_Surname] [varchar](50) NOT NULL,
[Cust_Email] [varchar](50) NOT NULL
) ON [PRIMARY]

CREATE TYPE [dbo].[customertype] AS TABLE(
[Cust_ID] [int] NULL,
[Cust_Name] [varchar](50) NULL,
[Cust_Surname] [varchar](50) NULL,
[Cust_Email] [varchar](50) NULL
)
GO


insert into Customers values (1,'1steven','1gerrard','1sg@liverpool.com')
insert into Customers values (2,'1jamie','1gerrard','1sg@liverpool.com')
insert into Customers values (3,'1Kamie','1Kerrard','1sg@liverpool.com')


CREATE procedure newcustomer(@cust_id varchar(50),@cust_name varchar(50),@cust_surname varchar(50),@cust_email varchar(50))

as

begin

IF exists (select 1 FROM Customers where Cust_ID=@cust_id)
update Customers
set Cust_Name=@cust_name,
Cust_Surname=@cust_surname,
Cust_Email=@cust_email
where Customers.Cust_ID= @cust_id
ELSE
insert into customers
(Cust_ID,Cust_Name,Cust_Surname,Cust_Email)
values (@cust_id,@cust_name,@cust_surname,@cust_email)
END

Exec newcustomer 3, '2jamie','2caragher','2jc@liverpool.com'
Exec newcustomer 4, '2kamie','2daragher','2kc@liverpool.com'
Exec newcustomer 6, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 7, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 8, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 9, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 10, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 11, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 12, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 13, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 14, '2kamie','2daragher','2kc@liverpool.com'
Exec newcustomer 16, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 17, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 18, '2lamie','2earagher','2lc@liverpool.com'
Exec newcustomer 19, '2lamie','2earagher','2lc@liverpool.com'








Alter procedure newcustomer(@Customer_details customertype READONLY)

as

begin

update Customers
set Cust_Name=b.Cust_name,
Cust_Surname=b.Cust_surname,
Cust_Email=b.Cust_email
from @Customer_details b
where Customers.Cust_ID=b.Cust_Id

insert into customers
select * from @Customer_details a
where not exists (Select 1 from Customers b where a.cust_id=b.Cust_ID)

end

DECLARE @Customer_details AS customertype
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (5, '3jamie','3caragher','3jc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (6, '3kamie','3daragher','3kc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (7, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (8, '3kamie','3daragher','3kc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (9, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (10, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (11, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (12, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (13, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (14, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (15, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (16, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (17, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (18, '3lamie','3earagher','3lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (19, '3lamie','3earagher','3lc@liverpool.com')


exec newcustomer1 @customer_details


ALTER procedure newcustomer (@Customer_details customertype READONLY)
as
BEGIN
MERGE Customers cs
USING @Customer_details cd
on cs.cust_id=cd.cust_id
WHEN MATCHED THEN
UPDATE
SET cs.Cust_Name=cd.Cust_name,
cs.Cust_Surname=cd.Cust_surname,
cs.Cust_Email=cd.Cust_email
WHEN NOT MATCHED BY TARGET THEN
INSERT (Cust_ID,cust_name,cust_surname,cust_email)
VALUES(cd.Cust_ID, cd.Cust_Name,cd.Cust_Surname,cd.Cust_Email);

END


DECLARE @Customer_details AS customertype
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (7, '4jamie','4caragher','4jc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (8, '4kamie','4daragher','4kc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (9, '5lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (15, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (16, '4jamie','4caragher','4jc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (17, '4kamie','4daragher','4kc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (22, '5lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (23, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (24, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (25, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (26, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (27, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (28, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (29, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (30, '4lamie','4earagher','4lc@liverpool.com')
INSERT INTO @Customer_details(Cust_ID,cust_name,cust_surname,cust_email)
VALUES (31, '4lamie','4earagher','4lc@liverpool.com')

exec newcustomer1 @customer_details

drop database temptest1456

SQL 2008 Logon Trigger

Create Logon Trigger
CREATE TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%excel%'
ROLLBACK
END

Drop Logon trigger
DROP TRIGGER logon_trigger_not_from_excel ON ALL SERVER


Which trigger appear in SQL Server
SELECT name, s.type_desc SQL_or_CLR,
is_disabled, e.type_desc FiringEvents
FROM sys.server_triggers s
INNER JOIN sys.server_trigger_events e ON
s.object_id = e.object_id

What is the metadata of Trigger?
SELECT t.name, m.definition
FROM sys.server_sql_modules m
INNER JOIN sys.server_triggers t ON
m.object_id = t.object_id

Refenrece:
Logon Triggers

Frederik Vandeputte : SQL Server Logon triggers part 2

Viewing DDL Triger Medatdata by joining sys.server_SQL_modules and sys.server_triggers

SQL 2008 control access from EXCEL, ODBC, ACCESS

We want change Application access from SQL Authentication to SSPI in order to trace who is access SQL table
We worry user may connect to SQL Server from Microsoft Excel, Microsft Access, SQL Server Management Studio
After google, we found out that is a features in SQL Server to control acccess of user: Logon Trigger

Below Code from Frederik Vandeputte-SQL Server Logon triggers part 2

CREATE TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%excel%'
ROLLBACK
END


After create Logon Trigger in SQL Server, I open Microsoft Excel, try connect to SQL Server, but connection successful!!
The result is not expected! SQL Server should reject the connection from Microsoft Excel
I can see the database and table in the SQL Server and successful retrieve the data and display in Excel File.
why the connection can establish even I already setup to rollback if connection from Excel?


Step to made connection from Microsoft Excel to local server:
Open Excel file->data->From Other Sources
From SQL Server-> keying .
"." for local server
log on credentials = User WIndows Authentication
I can view all database and table from local server

Now let troubleshoot why the trigger logon is not effective or no rollback the connection:

Below code from Vipul Shah's SQL Server Logon triggers part 2

CREATE DATABASE dbadb
go
use dbadb
CREATE TABLE myTest
(id int identity(1,1),
EventTime datetime,
LoginName varchar(50),
HostName varchar(50),
LoginType varchar(50),
ApplicationName varchar(100))

CREATE TRIGGER myTest_LogonTrigger ON ALL SERVER
WITH EXECUTE AS 'sa'
FOR LOGON
AS
BEGIN
declare @LogonTriggerData xml,
@EventTime datetime,
@LoginName varchar(50),
@HostName varchar(50),
@LoginType varchar(50)
set @LogonTriggerData = eventdata()
set @EventTime = @LogonTriggerData.value('(/EVENT_INSTANCE/PostTime)[1]', 'datetime')
set @LoginName = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginName)[1]', 'varchar(50)')
set @HostName = @LogonTriggerData.value('(/EVENT_INSTANCE/ClientHost)[1]', 'varchar(50)')
set @LoginType = @LogonTriggerData.value('(/EVENT_INSTANCE/LoginType)[1]', 'varchar(50)')
insert into dbadb..myTest values (@EventTime, @LoginName, @HostName, @LoginType,app_name())
END

Connect to SQLServer from Microsft Excel :
Open Excel file->data->From Other Sources
From SQL Server-> keying .
"." for local server
log on credentials = User WIndows Authentication and Press "Next"
Connection succesful and "Select Database and Table" page appear

Go backup to SQLServer and type the below query
select * from dbadb.dbo.mytest

It show the below result :

id	EventTime	LoginName	HostName	LoginType	ApplicationName
1	2011-01-01 20:25:04.467	testuser		Windows (NT) Login	2007 Microsoft Office system

It show that Excel connection appear as Application Name "2007 Microsoft Office system"

Let alter logon trigger not from excel as below
ALTER TRIGGER logon_trigger_not_from_excel
ON ALL SERVER FOR LOGON
AS
BEGIN
IF APP_NAME() LIKE '%2007 Microsoft Office system%' ROLLBACK
END

Make connection to SQL Server from Microsoft Excel again.
Connection Fail!
It show logon failed for login 'testuser' due to trigger execution.
We successful stop user from connection to SQLServer through Microsoft Excel.

I test few application and the associate application name detect from trigger logon listed as below:

Application	App_name()
Excel Office Excel 2007	2007 Micorosoft office System
Microsoft Office Access 2007	2007 Micorosoft office System
Microsoft SQLServer Management Studio	Microsoft SQL Server Management Studio - Transact-SQL IntelliSense
OSQL	OSQL-32

Reference:
Logon Triggers

Prevent Access to SQL Server 2005 databases from Microsoft Excel and Word

Frederik Vandeputte : SQL Server Logon triggers part 2