December 10, 2015 at 6:00 am
I have a couple of merge scripts that are run post deployment, one of which sets up a lookup table as per:
MERGE INTO [Languages] AS Target
USING (VALUES
('English','eng','en')
,('French','fra','fr')
) AS Source ([LanguageName],[Iso639Code],[Locale])
ON (Target.[Iso639Code] = Source.[Iso639Code])
WHEN MATCHED THEN
UPDATE SET
[LanguageName] = Source.[LanguageName],
[Locale] = Source.[Locale],
[UserModified] = @UpdatedBy,
[ModifiedDateTime] = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
INSERT([LanguageName],[Iso639Code],[Locale],[UserCreate],[CreateDateTime])
VALUES(Source.[LanguageName],Source.[Iso639Code],Source.[Locale],@UpdatedBy,GETDATE());
GO
This table has an [int] as the primary key. This script works well.
So my question is I now have another script which needs to look up the primary key from this table to insert into a second table.
MERGE INTO [Countries] AS Target
USING (VALUES
('England')
,('France')
) AS Source ([CountryName])
ON (Target.[CountryName] = Source.[CountryName])
WHEN MATCHED THEN
UPDATE SET
[CountryName] = Source.[CountryName],
[LanguageId] = (Select LanguageID from [Languages] where [LanguageName] = Source.[LanguageName]),
[Locale] = Source.[Locale],
[UserModified] = @UpdatedBy,
[ModifiedDateTime] = GETDATE()
WHEN NOT MATCHED BY TARGET THEN
INSERT([CountryName],LanguageId,[UserCreate],[CreateDateTime])
VALUES(Source.[CountryName],(Select LanguageID from [Languages] where [LanguageName] = Source.[LanguageName]),@UpdatedBy,GETDATE());
GO
Is this the correct procedure to link the two tables with the post deployment scripts?
December 10, 2015 at 6:07 am
Yes, exactly right.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 10, 2015 at 6:10 am
Except for one thing. If you have an ISO language table, there is no need to invent an int PK - use the ISO code instead.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
December 10, 2015 at 6:44 am
Thank you all for your quick response.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply