--:SETVAR userAdmin '00000000-0000-0000-0000-000000000000'\n--:SETVAR dtm SYSUTCDATETIME()\n\nSET IDENTITY_INSERT Common.DisbursementType ON<\/p>\n\n
MERGE INTO Common.DisbursementType AS [target]\nUSING\n(VALUES\n(1, 'Direct Deposit', 'Direct Deposit Refund', 1, 'DirectDeposit'),\n(2, 'Mail Me A Check', 'Mail Me A Refund Check', 1, 'RefundCheck'),\n(3, 'Mail Me A Debit Card', 'Mail Me A Debit For Refund', 1, 'RefundDebitCard'),\n(4, 'Direct Withdrawal', 'Direct Withdrawal For Payment', 1, 'DirectWithdrawal'),\n(5, 'Mail Check', 'Mail Check For Payment', 1, 'SendCheck'),\n(6, 'Credit Card', 'Credit Card For Payment', 1, 'CreditCard')\n)\nAS [source] (disbursementTypeId, name, [description], isEnabled, taxModelValue)\nON [target].disbursementTypeId = [source].disbursementTypeId<\/p>\n\n
WHEN MATCHED THEN\nUPDATE SET name = [source].name,\n[description] = [source].[description],\nisEnabled = [source].isEnabled,\ntaxModelValue = [source].taxModelValue,\nrowLastUpdatedUserId = $(userAdmin),\nrowLastUpdated = $(dtm)\nWHEN NOT MATCHED BY TARGET THEN\nINSERT (disbursementTypeId, name, [description], isEnabled, taxModelValue, rowCreated, rowCreatedUserId, rowLastUpdated, rowLastUpdatedUserId)\nVALUES (disbursementTypeId, name, [description], isEnabled, taxModelValue, $(dtm), $(userAdmin), $(dtm), $(userAdmin))\nWHEN NOT MATCHED BY SOURCE THEN\nDELETE;<\/p>\n\n
set IDENTITY_INSERT Common.DisbursementType OFF<\/code><\/pre>\n<\/p>\n
NOTE: You will see some SQLCMD variables in this script. That is because I modified this from a script that I using in an SSDT database project and the deployment process used SQLCMD mode. Those can be removed, if desired.<\/em><\/p>\nWrap-up<\/h3>\n
This\u00a0method is very simple to use because you only ever have to edit the VALUES section of the script when changing your lookup values. You control the exact identity values so that they match in all environments, and deploy using a\u00a0desired state<\/em> mentality.<\/p>\n","protected":false},"excerpt":{"rendered":"Each month, on the first Tuesday of the month, the announcement for the blog party T-SQL Tuesday comes out. Those that are interested then post their blogs, on the subject selected, on the second Tuesday of the month. If you\u2019ve never heard of T-SQL Tuesday it\u2019s a blog party started by Adam Machanic (b\/t) over […]<\/p>\n","protected":false},"author":1,"featured_media":541,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[3,4,6,7,8,9,10],"tags":[],"class_list":["post-141","post","type-post","status-publish","format-standard","has-post-thumbnail","hentry","category-administration","category-alwayson-availability-groups","category-career","category-community","category-continuous-integration-deployment","category-development","category-disaster-recovery"],"_links":{"self":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/141","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/comments?post=141"}],"version-history":[{"count":1,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/141\/revisions"}],"predecessor-version":[{"id":403,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/posts\/141\/revisions\/403"}],"wp:featuredmedia":[{"embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media\/541"}],"wp:attachment":[{"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/media?parent=141"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/categories?post=141"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/sqlhammer.com\/index.php\/wp-json\/wp\/v2\/tags?post=141"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}
![\"\"](\"\/wp-content\/uploads\/TSQL2sDay150x150-2.jpg\")
<\/p>\n