2 merge statements into 2 targets from 1 source

dgrafx
dgrafx used Ask the Experts™
on
Sql server 2k8

I'm on my phone right now so I won't be posting code but can later if necessary
 
I have a source CTE (srccte) and a merge statement following it using a target (tgttbl) -
Works perfect !
I then wanted to add another target - a second table.  So I wrote another Merge statement after the first Merge statement and I get an error saying that SRCCTE is undefined.

Note that if I comment out either the first or the second MERGE statement the entire code works perfect - it's only when trying to use both that the error occurs.

How would one go about using a second target table? Would I use just one Merge statement and work the second table into the one statement?

Thanks
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
View Solution Only
Database Administrator
Top Expert 2005
Commented:
Although you can reference a cte multiple times within a single query you can't across multiple queries.  You will either need to duplicate the cte logic for the second merge statement or dump the data into a temp table to be used by both merges.
dgrafx

Author

Commented:
Just read that - I was just going to modify the question but was messing with creating a temp table - do you have any code handy for creating a temp table?

thanks
Brian CroweDatabase Administrator
Top Expert 2005

Commented:
Just use the same CREAT TABLE DDL that you would use for a permanent table:

CREATE TABLE #Table
(
	ID				INT,
	Profile_ID		INT
);

Open in new window

dgrafx

Author

Commented:
ok cool - thanks !

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial