Skip to content

Instantly share code, notes, and snippets.

@davghouse
Created February 11, 2017 00:34
Show Gist options
  • Save davghouse/37d07ac6ac6fb21ddd1b1de8a5b94702 to your computer and use it in GitHub Desktop.
Save davghouse/37d07ac6ac6fb21ddd1b1de8a5b94702 to your computer and use it in GitHub Desktop.
Copy Nation procedure for Rooted World database
CREATE PROCEDURE [dbo].[CopyNation]
@id INT
AS
BEGIN
SET NOCOUNT ON;
SET XACT_ABORT ON;
BEGIN TRAN;
DECLARE @NationIDPairs TABLE (
ExistingID INT NOT NULL UNIQUE,
InsertedID INT NOT NULL UNIQUE
);
DECLARE @ProvinceIDPairs TABLE (
ExistingID INT NOT NULL UNIQUE,
InsertedID INT NOT NULL UNIQUE
);
MERGE INTO [dbo].[Nations] AS Target
USING (
SELECT *
FROM [dbo].[Nations]
WHERE [ID] = @id
) AS Source
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[Name],
[FoundedDate])
VALUES (
Source.[Name],
Source.[FoundedDate])
OUTPUT Source.[ID], Inserted.[ID]
INTO @NationIDPairs;
MERGE INTO [dbo].[Provinces] AS Target
USING (
SELECT
copy.*,
j0.InsertedID j0InsertedID
FROM [dbo].[Provinces] copy
JOIN @NationIDPairs j0
ON copy.[NationID] = j0.ExistingID
) AS Source
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[NationID],
[Name],
[Motto])
VALUES (
j0InsertedID,
Source.[Name],
Source.[Motto])
OUTPUT Source.[ID], Inserted.[ID]
INTO @ProvinceIDPairs;
MERGE INTO [dbo].[Residents] AS Target
USING (
SELECT
copy.*,
j0.InsertedID j0InsertedID
FROM [dbo].[Residents] copy
JOIN @ProvinceIDPairs j0
ON copy.[ProvinceID] = j0.ExistingID
) AS Source
ON 1 = 0
WHEN NOT MATCHED BY TARGET THEN
INSERT (
[ProvinceID],
[Name])
VALUES (
j0InsertedID,
Source.[Name]);
COMMIT TRAN;
END;
Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment