merge query with multiple tables

• charuhelaiya

  Old Hand

  Points: 301

  More actions

  April 26, 2011 at 10:59 am

  #249880

  Hi, i am trying to write a merge query for the insert to the table. but the source table does multiple joins. not sure how do i write the merge query. any ideas??

  below is the code that i want to turn into a merge query:

  INSERT INTO Location

  (

  Id,

  Number,

  Climate,

  Seismic,

  Wind,

  [Description],

  IsMetric,

  Created_By,

  Created_Date,

  ValuationId,

  Modified_By,

  Modified_Date,

  Longitude,

  Latitude,

  ModuleFlags,

  TaxRate

  )

  SELECT

  hl.MasterId,

  hl.Number,

  hl.Climate,

  hl.Seismic,

  hl.Wind,

  hl.[Description],

  hl.IsMetric,

  hl.Created_By,

  hl.Created_Date,

  @ValuationId,

  hl.Modified_By,

  hl.Modified_Date,

  hl.Longitude,

  hl.Latitude,

  hl.ModuleFlags,

  hl.TaxRate

  FROM HistoricalLocation hl

  JOIN HistoricalValuation hv ON hv.Id = hl.Id

  JOIN Valuation v ON v.Id = hv.MasterId

  WHERE v.Id = @ValuationId AND hl.Version = @Version AND hl.Sequence = 0

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  April 26, 2011 at 1:37 pm

  #1316156

  You can use a CTE in combination with a MERGE statement. Something along these lines should work for you:

  WITH cte

  AS (SELECT hl.MasterId,

  hl.Number,

  hl.Climate,

  hl.Seismic,

  hl.Wind,

  hl.[Description],

  hl.IsMetric,

  hl.Created_By,

  hl.Created_Date,

  @ValuationId,

  hl.Modified_By,

  hl.Modified_Date,

  hl.Longitude,

  hl.Latitude,

  hl.ModuleFlags,

  hl.TaxRate

  FROM HistoricalLocation hl

  JOIN HistoricalValuation hv ON hv.Id = hl.Id

  JOIN Valuation v ON v.Id = hv.MasterId

  WHERE v.Id = @ValuationId

  AND hl.Version = '@Version'

  AND hl.Sequence = 0

  )

  MERGE dbo.Location AS target_table

  USING cte AS source_table

  ONsource_table.MasterId = target_table.MasterId

  WHENMATCHED

  THENUPDATE

  SET target_table.field_name = source_table.field_name

  WHENNOT MATCHED BY TARGET

  THENINSERT

  (column_names_here)

  VALUES (

  'column values here'

  ) ;

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato

• g_u_u_bush

  SSC Enthusiast

  Points: 132

  More actions

  May 24, 2012 at 2:51 pm

  #1492823

  You can always create a view as the source to update from, then you could use as many tables as you want.

• Orlando Colamatteo

  SSC Guru

  Points: 182293

  More actions

  May 24, 2012 at 3:18 pm

  #1492836

  :exclamation: Note: this is a year-old thread.

  There are no special teachers of virtue, because virtue is taught by the whole community.
  --Plato