CODEWITHSUNDEEP
stored-proceduresmergesql-server-2008-r2
ValidfroM
ValidfroM

Reputation: 2817

How to use merge to insert value from a stored procedure

I am trying to do something like this:

merge MembershipTEST as T
using (select OrganisationID, Name From MembershipPending) as S
on  T.OrganisationID = S.OrganisationID
and T.Name = S.Name
when not matched then
insert (MembershipID,OrganisationID, Name) 
values(
(EXEC [dbo].[spGetNextIntKeyByTableName] 
@PKColName = 'MembershipID',@TableName = 'FWBMembership'),
S.OrganisationID, 
S.Name );

Bascially the identitykey is from a sp

Is it possible?

Update 1: Answer is NO

read the online doc http://msdn.microsoft.com/en-us/library/bb510625%28v=sql.105%29.aspx

VALUES ( values_list) Is a comma-separated list of constants, variables, or expressions that return values to insert into the target table. Expressions cannot contain an EXECUTE statement.

Upvotes: 2

Views: 1761

Answers (1)

Zane
Zane

Reputation: 4169

Build an SSIS package. Just create a new Data Flow task. Use two OLEDB sources make one of them execute the stored procedure and the other one select from the table you are looking to MERGE with. Make sure they are both ordered by the same thing. Go into advanced settings for each OLEDB source and set is sorted to true and then set the sort values for the items you are ORDERing BY. Then move both data flows to to a MERGE JOIN. Then send the Data flow to a Conditional Split and set as INSULL(OrganisationID). Use the resulting data flow to go to an OLEDB destination.

Sorry for the lack of visuals I will add them later when I'm on lunch hour to busy to add them now.

Upvotes: 1

Related Questions