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


Trouble Using Merge Statement


Trouble Using Merge Statement

Author
Message
LinksUp
LinksUp
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1730 Visits: 4640
Given the following tables and data:


declare @TextTable TABLE
(
Acct int,
TextDesc varchar(50)
)

declare @tempTable TABLE
(
Acct int
)

insert @TextTable (Acct, TextDesc)
SELECT 1, 'ABC' UNION ALL
SELECT 2, 'XYZ' UNION ALL
SELECT 4, '123' UNION ALL
SELECT 5, '456' UNION ALL
SELECT 6 ,'JKL'

insert @tempTable (Acct)
SELECT 2 UNION ALL
SELECT 3 UNION ALL
SELECT 4 UNION ALL
SELECT 5

declare @Acct int = 1



I am trying to build a merge statement that will take the TextDesc pointed to by @Acct and replicating it to all accounts in TextTable that are in the @tempTable. And if it is not found, insert the row.

So the desired output from @TextTable would look like:


Acct TextDesc
---- --------
1 ABC
2 ABC
3 ABC
4 ABC
5 ABC
6 JKL



If a merge is not possible, I am open to other solutions.

Any thoughts?

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
milos.radivojevic
milos.radivojevic
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 774
The following merge statement replicates text description from the FIRST row in the temmp variable @TextTable to the rows which position is defined in the second table variable @tempTable. From the provided description I did not find an explanation why the value 'ABC' is replicated (and not JKL, for instance) so I assumed that you know in advance which value should be replicated. Therefore the MERGE statement replicates the hardcoded value 'ABC' for demo purpose:



MERGE @TextTable AS target
USING (SELECT Acct, 'ABC' FROM @tempTable) AS source (Acct, TextDesc)
ON target.Acct = source.Acct
WHEN MATCHED THEN
UPDATE SET TextDesc = source.TextDesc
WHEN NOT MATCHED THEN
INSERT (Acct, TextDesc)
VALUES (source.Acct, source.TextDesc);




___________________________
Do Not Optimize for Exceptions!
LinksUp
LinksUp
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1730 Visits: 4640
Thanks for the reply. But, this does not solve my issues.

The reason the string has to be 'ABC' is because that is the row that is being pointed at by @Acct. You can see from my original statement that I need to be able to replicate the TextDesc of a specific row. The @tempTable tells me which rows in @textTable need to be updated or inserted. That is why row 6 is not touched.


I am trying to build a merge statement that will take the TextDesc pointed to by @Acct and replicating it to all accounts in TextTable that are in the @tempTable. And if it is not found, insert the row.


This is the code that I have written so far. It only does the update. I suspect that the inner join is preventing row 3 from the @tempTable from being part of the result set, thus the code for WHEN NOT MATCHED never executes.


MERGE into @TextTable T1
USING (
Select t.Acct a1, t3.textDesc, tt.Acct as newAcct
from @TextTable t
inner join @TextTable t3 on t3.Acct = @Acct
inner join @tempTable tt on tt.Acct = t.Acct
) T2 ON (T1.Acct = T2.A1)
WHEN MATCHED THEN
UPDATE SET
T1.textDesc = T2.TextDesc
WHEN NOT MATCHED THEN
INSERT (Acct, textDesc)
VALUES (T2.NewAcct, T2.TextDesc);



__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
milos.radivojevic
milos.radivojevic
Old Hand
Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)Old Hand (393 reputation)

Group: General Forum Members
Points: 393 Visits: 774
Sorry, I did not see the declaration:

declare @Acct int = 1



Then we will just slightly modify the statement from my previous post:



DECLARE @Acct INT = 1;

MERGE @TextTable AS target
USING (SELECT Acct, (SELECT TextDesc FROM @TextTable WHERE Acct = @Acct) FROM @tempTable) AS source (Acct, TextDesc)
ON target.Acct = source.Acct
WHEN MATCHED THEN
UPDATE SET TextDesc = source.TextDesc
WHEN NOT MATCHED THEN
INSERT (Acct, TextDesc)
VALUES (source.Acct, source.TextDesc);




___________________________
Do Not Optimize for Exceptions!
Tom Thomson
Tom Thomson
One Orange Chip
One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)One Orange Chip (25K reputation)

Group: General Forum Members
Points: 25873 Visits: 12494
LinksUp (2/16/2014)
I suspect that the inner join is preventing row 3 from the @tempTable from being part of the result set, thus the code for WHEN NOT MATCHED never executes.

That's exactly right; you have a spurious (meaningless coulum in your source table (T2) and don't have all the required rows. If you just generate useful columns it's easier to generate the required rowset. Replace your using clause by
USING (
select tt.Acct a1, t3.textDesc
from @tempTable tt cross join @TextTable t3 where t3.Acct=@Acct
) T2 ON (T1.Acct = T2.A1)


(I've use the same aliases t1, t2, t3 as you did to make it clear what is happening).

Tom

LinksUp
LinksUp
SSCommitted
SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)SSCommitted (1.7K reputation)

Group: General Forum Members
Points: 1730 Visits: 4640
Tom, Milos

Thanks for the solution. They both worked as advertised. This solution removed a large loop that was updating and inserting rows into a table. For about a 1000 rows it was taking 3 minutes. It is now 5 seconds.

Awesome!

__________________________________________________________________________________________________________
How to Post to get the most: http://www.sqlservercentral.com/articles/Best+Practices/61537/
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