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

Trouble Using Merge Statement Expand / Collapse
Author
Message
Posted Saturday, February 15, 2014 6:07 PM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:15 PM
Points: 352, Visits: 1,447
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/
Post #1541886
Posted Sunday, February 16, 2014 5:11 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 6:39 AM
Points: 158, Visits: 629
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!
Post #1541896
Posted Sunday, February 16, 2014 11:26 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:15 PM
Points: 352, Visits: 1,447
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/
Post #1541918
Posted Sunday, February 16, 2014 11:57 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 18, 2014 6:39 AM
Points: 158, Visits: 629
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!
Post #1541920
Posted Sunday, February 16, 2014 12:27 PM This worked for the OP Answer marked as solution


SSCertifiable

SSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiableSSCertifiable

Group: General Forum Members
Last Login: Yesterday @ 8:24 PM
Points: 7,688, Visits: 9,410
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
Post #1541922
Posted Monday, February 17, 2014 10:06 AM
Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Yesterday @ 8:15 PM
Points: 352, Visits: 1,447
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/
Post #1542212
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse