April 26, 2011 at 10:59 am
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
April 26, 2011 at 1:37 pm
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
ON source_table.MasterId = target_table.MasterId
WHEN MATCHED
THEN UPDATE
SET target_table.field_name = source_table.field_name
WHEN NOT MATCHED BY TARGET
THEN INSERT
(column_names_here)
VALUES (
'column values here'
) ;
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 24, 2012 at 2:51 pm
You can always create a view as the source to update from, then you could use as many tables as you want.
May 24, 2012 at 3:18 pm
:exclamation: Note: this is a year-old thread.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply