February 15, 2014 at 6:07 pm
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/
February 16, 2014 at 5:11 am
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!
February 16, 2014 at 11:26 am
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/
February 16, 2014 at 11:57 am
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!
February 16, 2014 at 12:27 pm
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
February 17, 2014 at 10:06 am
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/
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply