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

Help on Merge Into (Upsert) command Expand / Collapse
Author
Message
Posted Thursday, May 20, 2010 1:56 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 12, 2013 6:13 AM
Points: 47, Visits: 67
Hello,

I have stored procedure with the User Defined Table Type (UDTT), which is receiving collection rows from the Sync routines we wrote.
I have to update the table from the rows I received by UDTT parameter using the Upsert command as shown below:
1. USE [MyDatabase]
2. GO

3. MERGE INTO mytable as TARGET
4. USING mytable2 as Source
5. ON Target.id=Source.id
6. WHEN MATCHED THEN
7. UPDATE
8. SET Target.name=Source.name,
9. Target.Salary = Source.Salary
10. target.bonus = GetCalculatedBonus Source.id, source.salary
11. WHEN NOT MATCHED THEN
12. INSERT (id,name,salary, bonus)
13. VALUES (Source.id,Source.name,Source.Salary, StoredProcedure Call);

The tricky part is one of the column value should come from the StoredProcedure based on the parameters passed in. The storedprocedure has some DML commands (select and insert based on the situation)

But I cannot find the right way to call that stored procedure to comply with this query. The line 10 throws a syntax error and not sure how to satisfy that syntax. I tried all the tricks I know of. If I change that with EXEC target.bonus = GetCalculatedBonus Source.id, source.salary and didn’t work. Then I tried target.bonus = EXEC GetCalculatedBonus Source.id, source.salary , it doesn’t like this too.

So, I greatly appreciate if anyone could guide me on how to satisfy this syntax.

Thanks,

Vincent D’Souza
Post #925488
Posted Thursday, May 20, 2010 4:41 PM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 5:46 AM
Points: 316, Visits: 910
I am not sure I understand exactly what you are trying to do, but I dont think it is possible to call a stored procedure for each row in a query like that.

Maybe you could convert the calculation to a scalar user defined function instead ?

Or, you might be able to change the bonus procedure so it sets the correct bonus value in the source table before the MERGE.
Post #925577
Posted Friday, May 21, 2010 6:09 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 12, 2013 6:13 AM
Points: 47, Visits: 67
No, I cannot use function becuse if I understood correctly I cannot have Insert/update/Deletes in a scalar function OR may be in any function???
Post #925881
Posted Friday, May 21, 2010 6:20 AM


Old Hand

Old HandOld HandOld HandOld HandOld HandOld HandOld HandOld Hand

Group: General Forum Members
Last Login: Wednesday, September 10, 2014 5:46 AM
Points: 316, Visits: 910
Vincy-856784 (5/21/2010)
No, I cannot use function becuse if I understood correctly I cannot have Insert/update/Deletes in a scalar function OR may be in any function???


So, if I inderstand you correctly, your GetCalculatedBonus procedure has sideeffects - meaning that it inserts/updates/deletes rows.

In that case it is definitely not possible to use a UDF - it is also not possible to call such a procedure for each row in a query.

One suggestion is to modify the GetCalculatedBonus function so it actually calculates the correct bonus for each row in your source table.

You would then call this procedure once and then you would use MERGE to merge the source with the destination.
Post #925888
Posted Friday, May 21, 2010 8:43 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 12, 2013 6:13 AM
Points: 47, Visits: 67
Thanks for your quick response. Yes, the calling stored procedure has to insert rows in another table based on the passed in parameter if that table does not have one row for it.

Even the suggestion you provided won't work because i am doing bulk insert from User Define Table Type (UDTT).

The example i specified here is just a basic example and nothing to do with what i am trying to do. But in mine case the situation is more complex than i specified here. I get the collection of rows in the form of UDTT. But the UDTT data is partial data for the update/insert and other partial data is generated based off of other calculation through procedure or through joins.

So, the only thing, I can think of now is using the Cursor for the UDTT and iterate through one by one row and do the Upsert.

Meaning, call the sp based on the each row data from UDTT and store it in a variable and then do the Upsert.

Does that make sense?
Post #926022
Posted Friday, May 21, 2010 9:05 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, December 12, 2013 6:13 AM
Points: 47, Visits: 67
Oh boy! if i use cursor i have to fetch each row into variables. That sucks! can i fetch that row into a table? any syntax???
Post #926044
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse