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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:32 PM
Points: 11,194, Visits: 11,140
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
SQL Server MVP
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: Friday, June 27, 2014 12:06 PM
Points: 327, Visits: 331
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:32 PM
Points: 11,194, Visits: 11,140
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1568222
Posted Wednesday, May 7, 2014 9:35 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 1:59 PM
Points: 19, Visits: 282
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:32 PM
Points: 11,194, Visits: 11,140
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1568564
Posted Wednesday, May 7, 2014 12:42 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 1:59 PM
Points: 19, Visits: 282
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:32 PM
Points: 11,194, Visits: 11,140
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1568833
Posted Thursday, May 8, 2014 7:44 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Monday, July 28, 2014 1:59 PM
Points: 19, Visits: 282
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


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: 2 days ago @ 6:32 PM
Points: 11,194, Visits: 11,140
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
SQL Server MVP
SQLblog.com
@SQL_Kiwi
Post #1568917
« Prev Topic | Next Topic »

Add to briefcase «««123

Permissions Expand / Collapse