quote:

---

Originally posted by plap

Consider the following statement:
MERGE TargetTable AS t
USING (SELECT S1.*,S2.* from SourceTable1 as S1 left outer join SourceTable2 as S2 on S1.joinCol=s2.joinCol) AS s
ON (t.recordID = s.recordID)
WHEN MATCHED THEN
UPDATE ...
WHEN NOT MATCHED THEN
INSERT ...

This will work well. However I would like to add some new columns
To the source join result which depend on the success/failure of the join, by nesting further selects. For example:

MERGE TargetTable AS t
USING (
select *,dbo.MyUDF(newCol1) as ProcessedNewCol1
from (
select *,isNull(S2Col,'defaultColVal') as newCol1
from (SELECT S1.*,S2.* from SourceTable1 as S1 left outer join SourceTable2 as S2 on S1.joinCol=s2.joinCol)
)
) AS s ON (t.recordID = s.recordID)
WHEN MATCHED THEN
UPDATE ...
WHEN NOT MATCHED THEN
INSERT ...

This seems perfectly reasonable however fails with an obscure syntax error.
I have an initial attempt written on the adventureworks database:


sET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO

alter PROCEDURE sp_test

BEGIN

SET NOCOUNT ON;

CREATE TABLE #MyTempTable
(FirstName [nvarchar](50) NULL,
LastName [nvarchar](50) NULL,
SubTotal [money] NULL,
TaxAmt [money] NULL,
Freight [money] NULL,
TotalDue [money] NULL,
);

MERGE #MyTempTable AS TARGET
USING (SELECT * ,ISNULL([SubTotal],0) AS NewSubTotal
FROM (
SELECT [C].[FirstName],[C].[LastName], [S].[SubTotal],[S].[TaxAmt],[S].[Freight],[S].[TotalDue]
FROM [Sales].[SalesOrderHeader] AS S
JOIN [Person].[Contact] AS C ON [S].[ContactID] = [C].[ContactID]
WHERE [OrderDate]='2004-07-31'
)AS T
) AS SOURCE (FirstName,LastName,SubTotal,TaxAmt,Freight,TotalDue)
ON (1=0)
WHEN NOT MATCHED THEN
INSERT ([FirstName],[LastName],[SubTotal],[TaxAmt],[Freight],[TotalDue])
VALUES ([SOURCE].[FirstName],[SOURCE].[LastName],[SOURCE].[SubTotal],[SOURCE].[TaxAmt],[SOURCE].[Freight],[SOURCE].[TotalDue])

;


SELECT * FROM #MyTempTable;
DROP TABLE #MyTempTable;


END
GO

I am also aware that this logic can be applied at the insert/update level but this will not work for my real life situation.

Thanks for the help,

Gary

---

CREATE TABLE #MyTempTable
(FirstName [nvarchar](50) NULL,
LastName [nvarchar](50) NULL,
SubTotal [money] NULL,
TaxAmt [money] NULL,
Freight [money] NULL,
TotalDue [money] NULL, 
);

MERGE #MyTempTable AS TARGET
USING (SELECT * ,ISNULL([SubTotal],0) AS NewSubTotal
FROM (
SELECT [C].[FirstName],[C].[LastName], [S].[SubTotal],[S].[TaxAmt],[S].[Freight],[S].[TotalDue] 
FROM [Sales].[SalesOrderHeader] AS S 
JOIN [Person].[Contact] AS C ON [S].[ContactID] = [C].[ContactID]
WHERE [OrderDate]='2004-07-31'
) AS s
) AS SOURCE (FirstName,LastName,SubTotal,TaxAmt,Freight,TotalDue)
ON (1=0) 
WHEN NOT MATCHED THEN	
INSERT ([FirstName],[LastName],[SubTotal],[TaxAmt],[Freight],[TotalDue])
VALUES ([SOURCE].[FirstName],[SOURCE].[LastName],[SOURCE].[SubTotal],[SOURCE].[TaxAmt],[SOURCE].[Freight],[SOURCE].[TotalDue])

;

quote:

---

Originally posted by plap

Thanks Visakah that solved it. I thought aliases were always optional. The unmatched condition is just for testing purposes.

---