June 7, 2012 at 7:10 am
Hi,
Please look at the below query and suggest if there is a cleaner way to achieve this.
This is the script to create sample data.
USE tempdb;
CREATE TABLE Src
(
Id INT ,
LastName VARCHAR(10)
);
CREATE TABLE Dest
(
Id INT ,
LastName VARCHAR(10)
);
INSERT Src
VALUES ( 1, 'Jane' ),
( 2, 'John' ),
( 3, 'You' );
INSERT Dest
SELECT TOP 2
Id ,
LastName
FROM Src;
Depending on a variable, i have execute either query1 or query2. As you will see at the end of the script, the only difference is output clause. I've used IF ELSE, but the script doesn't look good. Is there a "cleaner" and efficient way to write this.
DECLARE @Logging_Level CHAR(10)
SET @Logging_Level = 'None'
IF @Logging_Level = 'None'
BEGIN
MERGE Dest D
USING Src S
ON S.Id = D.Id
WHEN MATCHED and S.LastName != D.LastName
THEN
UPDATE SET
D.LastName = S.LastName
WHEN NOT MATCHED
THEN
INSERT ( Id, LastName )
VALUES
( S.Id ,
S.LastName
);
END
ELSE
BEGIN
MERGE Dest D
USING Src S
ON S.Id = D.Id
WHEN MATCHED and S.LastName != D.LastName
THEN
UPDATE SET
D.LastName = S.LastName
WHEN NOT MATCHED
THEN
INSERT ( Id, LastName )
VALUES
( S.Id ,
S.LastName
) OUTPUT
INSERTED.Id [NewID] ,
INSERTED.LastName [NewLastName];
END
Thanks for your help.
Sam Vanga
http://SamuelVanga.com
June 7, 2012 at 7:41 am
I don't think it looks bad, it does what you want.
Your only other option is to always use OUTPUT into a table variable. Then test the logging flag after the merge statement and select from that according to the variable:
MERGE Dest D
USING Src S
ON S.Id = D.Id
WHEN MATCHED and S.LastName != D.LastName
THEN
UPDATE SET
D.LastName = S.LastName
WHEN NOT MATCHED
THEN
INSERT ( Id, LastName )
VALUES
( S.Id ,
S.LastName
) OUTPUT into @OutPutTable
INSERTED.Id [NewID] ,
INSERTED.LastName [NewLastName];
END
IF @Logging_Level <> 'None'
select * from @OutPutTable
The probability of survival is inversely proportional to the angle of arrival.
June 7, 2012 at 8:03 am
Thanks Sturner.
Sam Vanga
http://SamuelVanga.com
June 7, 2012 at 7:47 pm
As I have learned in my brief time posting to this forum, there's always a better way!
Not sure if this is but I'll take a shot.
CREATE TYPE MyTable AS TABLE
(
Id INT ,
LastName VARCHAR(10)
);
GO
DECLARE @Src MyTable
DECLARE @Dest MyTable
DECLARE @Output MyTable
INSERT @Src
VALUES ( 1, 'Jane' ),
( 2, 'John' ),
( 3, 'You' );
INSERT @Dest
SELECT TOP 2
Id ,
LastName
FROM @Src;
DECLARE @Logging_Level CHAR(10)
SET @Logging_Level = 'On'
MERGE @Dest D
USING @Src S
ON S.Id = D.Id
WHEN MATCHED and S.LastName != D.LastName
THEN
UPDATE SET D.LastName = S.LastName
WHEN NOT MATCHED
THEN
INSERT ( Id, LastName )
VALUES ( S.Id , S.LastName)
OUTPUT Inserted.ID, Inserted.LastName
INTO @Output;
IF @Logging_Level <> 'None'
SELECT ID, NewLastName=LastName FROM @Output
SELECT * FROM @Src
SELECT * FROM @Dest
DROP TYPE MyTable
You can ignore my use of table variables/TYPE and just switch to using a third temp table (or table variable). The code is shorter, just not sure it's better.
My thought question: Have you ever been told that your query runs too fast?
My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.
Need to UNPIVOT? Why not CROSS APPLY VALUES instead?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply