Initializing Tables in SSDT using the Post Deployment Script

By Bob Blackburn

Since the Post Deployment Script runs in SQL CMD Mode, you are limited to the commands you can use. The Merge statement is not only allowed; but, is very readable where ever you use it. Below are two examples of the Merge statement you can use to load data in the Post Deployment Script. The first uses a CTE because of a select against other tables that have been previously loaded. The second is using inline values. If you do not wish to delete any existing data, remove the When Not Matched by Source option.

CTE Example

With AccessMatrix (UserFunctionId, UserRoleId, SubscriptionId, ClientStatusId, ReadWriteFlag)
as (SELECT userfunctionid,
        userroleid,
        subscriptionid,
        clientstatusid,
        'W' AS ReadWriteFlag
 FROM   vc.userfunction uf,
        vc.userrole ur,
        vc.subscription s,
        vc.clientstatus cs
 WHERE  {Write criteria}
 UNION
 SELECT userfunctionid,
        userroleid,
        subscriptionid,
        clientstatusid,
        'R' AS ReadWriteFlag
 FROM   vc.userfunction uf,
        vc.userrole ur,
        vc.subscription s,
        vc.clientstatus cs
 WHERE  {Read criteria}) 
 

MERGE INTO vc.useraccess AS Target

USING AccessMatrix

ON Target.UserFunctionId = AccessMatrix.UserFunctionId
and target.UserRoleId = AccessMatrix.UserRoleId
and target.SubscriptionId = AccessMatrix.SubscriptionId
and target.ClientStatusId = AccessMatrix.ClientStatusId


-- update matched rows
WHEN MATCHED
THEN
UPDATE set ReadWriteFlag = AccessMatrix.ReadWriteFlag

-- insert new rows
WHEN NOT MATCHED BY TARGET THEN
INSERT (UserFunctionId, UserRoleId, SubscriptionId, ClientStatusId, ReadWriteFlag)
VALUES (UserFunctionId, UserRoleId, SubscriptionId, ClientStatusId, ReadWriteFlag)

-- delete rows that are in the target but not the source
WHEN NOT MATCHED BY SOURCE THEN
DELETE;

 
Values Example
 
MERGE INTO [cw].TransactionType AS Target
USING (VALUES
(1, N'Sale'),
(2, N'Lease'),
(3, N'Rental'),
(4, N'Asset/Goods Movement'),
(5, N'Inventory Removal')
)
AS Source (TransactionTypeID, TransactionTypeName)
ON Target.TransactionTypeID = Source.TransactionTypeID

-- update matched rows
WHEN MATCHED
THEN
UPDATE set TransactionTypeName = source.TransactionTypeName

-- insert new rows
WHEN NOT MATCHED BY TARGET THEN
INSERT (TransactionTypeID, TransactionTypeName)
VALUES (TransactionTypeID, TransactionTypeName)

-- delete rows that are in the target but not the source
WHEN NOT MATCHED BY SOURCE THEN
DELETE;

 
 

Labels: , , , , ,