Seed Data using merge statement including data from external table

I’ve been provided an excel file with data that I am supposed to merge into a database. From this data I am concatinating a string in this format:

('Maastricht','demolitionCost',2.64,86.64,26.12,'€/m²','EUR',1),

'Maastricht' here is the name of a city, which I need up in a foreign table during the insert. This is the script that I have come up with:

MERGE INTO [Cost] AS Target
USING (VALUES
    ('Maastricht','demolitionCost',2.64,86.64,26.12,'€/m²','EUR',1),
    ('Maastricht','buildCost',81.86,1863.54,679.28,'€/m²','EUR',1),
    ('Maastricht','refurbishmentCost',81.86,1863.54,679.28,'€/m²','EUR',1),
    ('Amsterdam','demolitionCost',2.77,90.97,27.42,'€/m²','EUR',1),
    ('Amsterdam','buildCost',85.93,1956.72,713.24,'€/m²','EUR',1),
    ('Amsterdam','refurbishmentCost',85.93,1956.72,713.24,'€/m²','EUR',1)
)
AS Source (City, [Name], MinValue, MaxValue, MedianValue, MeasurementUnit, CurrencyCode, IsActive)
ON Target.[Name] = Source.[Name] AND 
   Target.[MinValue] = Source.[MinValue] AND
   Target.[MaxValue] = Source.[MaxValue] AND
   Target.[MedianValue] = Source.[MedianValue] AND
   Target.[MeasurementUnit] = Source.[MeasurementUnit] AND
   Target.[CurrencyCode] = Source.[CurrencyCode]

WHEN NOT MATCHED BY TARGET THEN
INSERT (LocationId, [Name],[MinValue], [MaxValue], [MedianValue], [MeasurementUnit], [CurrencyCode], [CreatedDate], [ModifiedDate], [IsActive])
VALUES (
    (Select Top(1) Id from location loc where loc.City = City), 
    [Name], 
    [MinValue], 
    [MaxValue], 
    [MedianValue],
    [MeasurementUnit],
    [CurrencyCode],
    GetDate(),
    GetDate(),
    IsActive);

GO

To Replace the city name with the actual foreign key I tried to simply do a select from the location table, which unfortunately only ever gives me the first Id it does find, which results in wrong relationships.

What is the correct way to lookup the foreign-key for a city name, so it does insert the correct value during the insert?

Answer

You can join the locations table to the values like this:

MERGE INTO [Cost] AS Target
USING 
    (
        SELECT Id, [Name], MinValue, MaxValue, MedianValue, MeasurementUnit, CurrencyCode, IsActive
        FROM location AS loc 
        JOIN (VALUES
                ('Maastricht','demolitionCost',2.64,86.64,26.12,'€/m²','EUR',1),
                ('Maastricht','buildCost',81.86,1863.54,679.28,'€/m²','EUR',1),
                ('Maastricht','refurbishmentCost',81.86,1863.54,679.28,'€/m²','EUR',1),
                ('Amsterdam','demolitionCost',2.77,90.97,27.42,'€/m²','EUR',1),
                ('Amsterdam','buildCost',85.93,1956.72,713.24,'€/m²','EUR',1),
                ('Amsterdam','refurbishmentCost',85.93,1956.72,713.24,'€/m²','EUR',1)
             ) AS  V(City, [Name], MinValue, MaxValue, MedianValue, MeasurementUnit, CurrencyCode, IsActive)
            ON loc.City = V.City
)
AS Source 
ON Target.[Name] = Source.[Name] AND 
   Target.[MinValue] = Source.[MinValue] AND
   Target.[MaxValue] = Source.[MaxValue] AND
   Target.[MedianValue] = Source.[MedianValue] AND
   Target.[MeasurementUnit] = Source.[MeasurementUnit] AND
   Target.[CurrencyCode] = Source.[CurrencyCode]

WHEN NOT MATCHED BY TARGET THEN
INSERT (LocationId, [Name],[MinValue], [MaxValue], [MedianValue], [MeasurementUnit], [CurrencyCode], [CreatedDate], [ModifiedDate], [IsActive])
VALUES (
    Id, 
    [Name], 
    [MinValue], 
    [MaxValue], 
    [MedianValue],
    [MeasurementUnit],
    [CurrencyCode],
    GetDate(),
    GetDate(),
    IsActive
);

GO