SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


comparison in the merge statement about null values


comparison in the merge statement about null values

Author
Message
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36090 Visits: 11361
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
tiffanyjanetblack
tiffanyjanetblack
Mr or Mrs. 500
Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)Mr or Mrs. 500 (571 reputation)

Group: General Forum Members
Points: 571 Visits: 357
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. ;-)
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36090 Visits: 11361
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
mburbea
mburbea
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 465
Paul do you have a similar trick that will work in the output clause? Output statements do not allow subqueries Sad
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


Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36090 Visits: 11361
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
mburbea
mburbea
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 465
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


Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36090 Visits: 11361
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
mburbea
mburbea
SSC Journeyman
SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)SSC Journeyman (96 reputation)

Group: General Forum Members
Points: 96 Visits: 465
That's what I figured. I hate scalar udfs in sql server as they tend to perform so poorly Sad
Paul White
Paul White
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: General Forum Members
Points: 36090 Visits: 11361
mburbea (5/8/2014)
That's what I figured. I hate scalar udfs in sql server as they tend to perform so poorly Sad

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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search