0

I need to merge two tables in following way:

  • Target has one extra Column ID. This Id is coming FROM another Single Column Master Table.
  • While Inserting the Record in Merge Statement I need to INSERT a new row into mater table and use its id to insert into TARGET table.

I have created a Stored Procedure that Inserts and returns newly inserted ID. Now the Problem is inside SQL Merge, we can't call a stored Proc.

What could be the solution of this issue? Cant use Scalar functions as INSERT can't be performed in Functions.

DECLARE @temp INT
MERGE dbo.mytabletarget T
USING dbo.mytableSource S
ON T.refId=S.RefId
WHEN MATCHED  THEN
UPDATE 
SET T.col1=S.col1,
    T.Col2=S.Col2
WHEN NOT MATCHED BY TARGET THEN
INSERT (Id,col1,col2)
VALUES({Here i need value from SP. SP simply Inserts a new Id into master table and Returns it},S.col1,S.col2);
GO
InTheWorldOfCodingApplications
  • 2,526
  • 6
  • 46
  • 87
  • You could try using the `INSERT ... EXEC ...` syntax to execute your SP into a temp table or table variable, then merge with that? – Bridge Jan 15 '13 at 12:50

1 Answers1

0

What could be the solution of this issue?

Do not use a stored procedure. Obvious, isn't it?

For a merge statement, you pretty much are stuck with doing the commands right there in the statement. Merge focuses on ETL loads and has advantages as well as limitations.

Basically, put the logic into the merge statement.

While Inserting the Record in Merge Statement I need to INSERT a new row into mater table and use its id to insert into TARGET table.

Hm, lookup table maintenance?

The regular approach for that is ti make sure the lookup table is filled first (in a separate statement). ETL (and that is where merge comes from) often works along stages for that particular reason.

Sorry, I do not have a better solution either ;(

TomTom
  • 61,059
  • 10
  • 88
  • 148
  • How i can fill Look up table inside Merge Statement – InTheWorldOfCodingApplications Jan 15 '13 at 13:24
  • Not inside, in a SEPARATE one. "works in stages". First fill the lookup table, then fill the fact data. This is standard. Alternatively you could try merging into a view that has a instead of insert trigger that handles the lookup. – TomTom Jan 15 '13 at 13:26
  • I need to INSERT INTO Look up table for each INSERT inside MERGE. Can you edit code in Original Post to elaborate this. Thanks – InTheWorldOfCodingApplications Jan 15 '13 at 13:33
  • @user1711287 You need to start thinking like a programmer. You can not do that in the one merge, so run two merges. And if you want me to write YOUR code, I have a daily rate for this. This is a Q&A Site, not a "do my work for me for free" location. – TomTom Jan 15 '13 at 13:34
  • Can you please elaborate the concept of using two Merges ? particularly for my scenario. – InTheWorldOfCodingApplications Jan 15 '13 at 16:30
  • Well, you execute one SQL Statement, then the next one. First fills in all the missing lookups. Second then does the insert and all lookups are there. Maybe you should talk to your lead developer for help? I mean, this starts getting on "trainee/homework" level. – TomTom Jan 15 '13 at 16:33