Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase «««123

comparison in the merge statement about null values Expand / Collapse
Author
Message
Posted Thursday, August 23, 2012 7:11 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:58 AM
Points: 9,928, Visits: 11,248
sqlfriends (8/23/2012)
So in the merge statement, Right below when matched statement I can add where not exists like below ? Thanks

Don't just copy and paste, read the article and understand it! You have an extra 'WHERE' that would cause a syntax error. I'm amazed you replied without trying it first.




Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Post #1349413
Posted Tuesday, May 6, 2014 4:16 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Thursday, March 19, 2015 1:31 PM
Points: 343, Visits: 341
Paul - THANK YOU for this * 100000000000000000000

thank you thank you thank you.

And yes I read the whole article, and sent on to my jr. dba.


Post #1568219
Posted Tuesday, May 6, 2014 4:36 PM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:58 AM
Points: 9,928, Visits: 11,248
tiffanyjanetblack (5/6/2014)
Paul - THANK YOU for this * 100000000000000000000

thank you thank you thank you.

And yes I read the whole article, and sent on to my jr. dba.

No worries.




Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Post #1568222
Posted Wednesday, May 7, 2014 9:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 3, 2015 8:42 AM
Points: 21, Visits: 320
Paul do you have a similar trick that will work in the output clause? Output statements do not allow subqueries
I have a store procedure that is called with a row of values and invokes a merge statement. I capture the output of the merge statement into a temp table and I want to check if any value has changed, if so I create an audit.
Right now I use this contraption, but ideally I'd like to simplify it as I find the isnull(nullif statement horribly ugly:
output
inserted.$identity [id],
convert(binary(1),isnull(nullif(inserted.colName1,deleted.colName1),nullif(deleted.colName1,inserted.colName1))),
convert(binary(1),isnull(nullif(inserted.colName2,deleted.colName2),nullif(deleted.colName2,inserted.colName2)))
into @ai(id,colName1,colName2)
...
insert into audit(TableName,attribute,Value,Identifier,auditdate)
select 'TableName',target.attribute,target.value,z.id,@auditDate
from @ai z
cross apply (VALUES
('colName1',convert(sql_variant,@colName1),z.colName1),
('colName2',convert(sql_variant,@colName2),z.colName2),
) target (attribute,value,includeIfNotNull)
where target.includeIfNotNull is not null

Post #1568542
Posted Wednesday, May 7, 2014 9:58 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:58 AM
Points: 9,928, Visits: 11,248
mburbea (5/7/2014)
Paul do you have a similar trick that will work in the output clause? Output statements do not allow subqueries

You can't put the EXISTS...INTERSECT check in the MERGE statement body?

Can you post a complete repro (perhaps as a new question?) I sort of see what you're getting at, but a complete code example with sample data and expected output always helps avoid unnecessary to-and-fro.




Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Post #1568564
Posted Wednesday, May 7, 2014 12:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 3, 2015 8:42 AM
Points: 21, Visits: 320
Sure understood. Here is an example script that shows what I want.
Basically, I want to only create an audit when a user changes data for the table "tableName". I use the output clause right now to determine if a change was made. The audit trail that this query outputs will ignore the initial inserts of null (which is what I want), and report the changes that each of the users made and when they made them. This should be a self-contained repo. The procedure is pretty similar to what is actually called.

I absolutely despise how verbose the way the is variable changed check is
convert(binary(1),isnull(nullif(inserted.col1,deleted.col1),nullif(deleted.col1,inserted.col1))),
[/sql]
It would be much simpler to have something like convert(binary(1),(select inserted.col1 intersect inserted.col2))

[code="sql"]
use tempdb;
GO
if (object_id('tableName') is not null)
drop table tableName

create table tableName
(
id int not null identity primary key clustered,
col1 int null,
col2 varchar(255) null,
isActive bit not null default (1)
)
if (object_id('auditName') is not null)
drop table tableName
create table audit
(
auditId int not null identity primary key clustered,
tableName sysname not null,
attribute sysname not null,
value sql_variant null,
tableId int not null,
auditUser sysname not null,
auditdate datetime not null
)
if(object_id('upsertTableName') is not null)
drop procedure upsertTableName
Go
create procedure upsertTableName
(
@Id int,
@col1 int,
@col2 varchar(255),
@isActive bit,
@userName varchar(255)
)
AS
BEGIN
declare @auditDate datetime = getdate();
declare @ai table(
id int,
col1 bit,
col2 bit,
isActive bit
)
begin tran
;merge tableName as target
using (select @Id [id],
@col1 [col1],
@col2 [col2],
@isActive [isActive]) src
on target.id=src.id
when matched then
update set
target.col1 = src.col1,
target.col2 = src.col2,
target.isActive = src.isActive
when not matched by target then
insert(col1,col2,isActive)
values(col1,col2,isActive)
output
inserted.$identity [id],
convert(binary(1),isnull(nullif(inserted.col1,deleted.col1),nullif(deleted.col1,inserted.col1))),
convert(binary(1),isnull(nullif(inserted.col2,deleted.col2),nullif(deleted.col2,inserted.col2))),
convert(binary(1),isnull(nullif(inserted.isActive,deleted.isActive),nullif(deleted.isActive,inserted.isActive)))
into @ai(id,col1,col2,isActive)
output inserted.id [id];

insert into audit(TableName,attribute,Value,TableId,auditdate,auditUser)
select 'TableName',target.attribute,target.value,z.id,@auditDate,@userName
from @ai z
cross apply (VALUES
('col1',convert(sql_variant,@col1),z.col1),
('col2',convert(sql_variant,@col2),z.col2),
('isActive',convert(sql_variant,@isActive),z.isActive)
) target (attribute,value,includeIfNotNull)
where target.includeIfNotNull is not null
commit
END
GO
declare @t table(id int)
declare @id int;
insert @t
exec upsertTableName null,null,null,1,'jim'
select @id = (select * from @t)
waitfor delay '00:00:00.5'
;exec upsertTableName @id,1,'a',1,'eric'
waitfor delay '00:00:00.5'
;exec upsertTableName @id,2,'a',1,'susan'
waitfor delay '00:00:00.5'
;exec upsertTableName @id,null,null,0,'john'
select attribute,value,audituser FROM audit
where tableId = @id
and tableName = 'tableName'
order by auditDate
/*
attribute value audituser
isActive 1 jim
col1 1 eric
col2 a eric
col1 2 susan
col1 NULL john
col2 NULL john
isActive 0 john
*/

drop table audit
drop table tableName
drop procedure upsertTableName

Post #1568656
Posted Thursday, May 8, 2014 5:28 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:58 AM
Points: 9,928, Visits: 11,248
mburbea (5/7/2014)
I absolutely despise how verbose the way the is variable changed check is

The only idea that leaps to mind is that while the OUTPUT clause does not allow a subquery, it does allow a scalar function:

CREATE FUNCTION dbo.IsDistinctFrom
(
@value1 sql_variant,
@value2 sql_variant
)
RETURNS bit
WITH SCHEMABINDING
AS
BEGIN
RETURN
CASE WHEN NOT EXISTS
(
SELECT @value1
INTERSECT
SELECT @value2
)
THEN 1
ELSE 0
END;
END;

The OUTPUT clause would then become something like:

    OUTPUT
Inserted.$identity AS id,
dbo.IsDistinctFrom(Inserted.col1, Deleted.col1) AS col1,
dbo.IsDistinctFrom(Inserted.col2, Deleted.col2) AS col2,
dbo.IsDistinctFrom(Inserted.isActive, Deleted.isActive) AS isActive

It's not perfect, but perhaps it gives you some ideas.




Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Post #1568833
Posted Thursday, May 8, 2014 7:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Friday, April 3, 2015 8:42 AM
Points: 21, Visits: 320
That's what I figured. I hate scalar udfs in sql server as they tend to perform so poorly
Post #1568890
Posted Thursday, May 8, 2014 8:19 AM


SSCrazy Eights

SSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy EightsSSCrazy Eights

Group: General Forum Members
Last Login: Today @ 3:58 AM
Points: 9,928, Visits: 11,248
mburbea (5/8/2014)
That's what I figured. I hate scalar udfs in sql server as they tend to perform so poorly

Me too. I rarely (very rarely!) use them or recommend them, but in this case it seems guaranteed to only ever operate on a single data item, so it should be ok. Depends whether you think the neatness is worth forcing yourself to use a scalar function, I suppose!




Paul White
SQLPerformance.com
SQLblog.com
@SQL_Kiwi
Post #1568917
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse