CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sqlsql-server-2012
RMu
RMu

Reputation: 869

MERGE statement inserts incorrect data TSQL

I'm trying to use MERGE statement to update data in a table with changes and insert data if the row is not present. Even though the merge conditions are met the data that is being inserted has incorrect data.

These are the sample data that has to be inserted::

PrimaryFundName SsFundCode  PortfolioName   DataSourceName  AssetClass  SubAssetClass   Strategy    SubStrategy DataSourceFundName        CodeRedFundName
Ventures         33EU        Endowment      SS_Private Edge     Fixed Income   Private Equity   Venture Capital Growth Equity   137 Ventures III    137 Ventures III  
Ventures         33EU        Endowment      SS_Accounting       Equity    Private Equity    Venture Capital Growth Equity        137 VENTURES III LP 137 Ventures III

But the data inserted was below::

PrimaryFundName SsFundCode  PortfolioName   DataSourceName  AssetClass  SubAssetClass   Strategy    SubStrategy DataSourceFundName        CodeRedFundName
Ventures         33EU        Endowment      SS_Private Perf     Fixed Income   Private Equity   Venture Capital Growth Equity   137 Ventures III    137 Ventures III  
Ventures         33EU        Endowment      SS_Accounting       Equity    Private Equity    Venture Capital Growth Equity        137 VENTURES III 137 Ventures III

DataSource and Datasource Fund name was incorrect. Below is my MERGE Statement::

MERGE  FundRel FR
USING #FundTemp Tmp
 ON (FR.DataSourceFundName = Tmp.DataSourceFundName AND FR.PortFolioID = 
Tmp.PortFolioID AND FR.DataSourceID = Tmp.DataSourceID)
WHEN MATCHED THEN
  UPDATE  SET  FR.DataSourceFundName = Tmp.NewDataSourceFundName
  , FR.CodeRedFundName = Tmp.NewCodeRedFundName
  , FR.AssetClassID = Tmp.AssetClassID
  , FR.SubAssetClassID = Tmp.SubAssetClassID
   , FR.StrategyID = Tmp.StrategyID
 , FR.SubStrategyID = Tmp.SubStrategyID

WHEN NOT MATCHED BY TARGET THEN
 INSERT ([PortfolioID]
  ,[DataSourceID]
  ,[PrimaryFundID]
  ,[DataSourceFundName]
  ,[FundNumber]
  ,[AssetClassID]
  ,[SubAssetClassID]
  ,[StrategyID]
  ,[SubStrategyID]
  ,[CodeRedFundName])
  VALUES (Tmp.[PortfolioID]
  ,Tmp.[DataSourceID]
  ,Tmp.[PrimaryFundID]
  ,Tmp.[DataSourceFundName]
  ,Tmp.FundCode
  ,Tmp.[AssetClassID]
  ,Tmp.[SubAssetClassID]
  ,Tmp.[StrategyID]
  ,Tmp.[SubStrategyID]
  ,Tmp.[CodeRedFundName]);

Please advise what I'm doing wrong. Thanks!

Update: I got to the issue. The incorrect data was coming out of a funky JOIN.

Upvotes: 0

Views: 54

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

your problem has nothing to do with merge

and how can we say,why you are getting wrong result unless you don't provide other details like table sample data and structure.

or you can debug your own like this,

select tmp.*
from FundRel FR
inner join #FundTemp Tmp
 ON (FR.DataSourceFundName = Tmp.DataSourceFundName 
AND FR.PortFolioID = Tmp.PortFolioID 
AND FR.DataSourceID = Tmp.DataSourceID)

Upvotes: 2

Matt M
Matt M

Reputation: 106

Double check the WHEN MATCHED block. FR.CodeRedFundName = Tmp.NewCodeRedFundName the column names are different than the example data you've provided above.

Upvotes: 1

Related Questions