sql-server – 如何在SSDT项目中包含自定义数据迁移和静态/引用

大多数生成的迁移脚本正在删除和(重新)创建过程,函数,索引和执行任何结构更改,以及部署后脚本中包含的一些数据脚本.这两个与数据相关的项目我想知道如何在项目中最好地构建：

>版本之间需要自定义数据迁移
>静态或参考数据

版本之间需要自定义数据迁移

有时我们希望在升级过程中执行一次性数据迁移,我不确定将其纳入我们的SSDT项目的最佳方式.例如,最近我添加了一个新的位列dbo.Charge.HasComments来包含基于另一个表的(冗余)派生数据,并将通过触发器保持同步.令人讨厌但必要的性能改进(仅在仔细考虑和测量后才添加).作为升级的一部分,SSDT生成的发布脚本将包含必要的ALTER TABLE和CREATE TRIGGER语句,但我还想根据另一个表中的数据更新此列：

update dbo.Charge 
set HasComments = 1 
where exists ( select * 
               from dbo.ChargeComment 
               where ChargeComment.ChargeId = Charge.ChargeId ) 
and HasComments = 0

在我的SSDT项目中包含此数据迁移脚本的最佳方法是什么？

目前,我将每种类型的迁移都放在一个单独的文件中,该文件包含在部署后脚本中,因此我的Post-Deployment脚本最终看起来像这样：

-- data migrations
:r "data migrationUpdate dbo.Charge.HasComments if never populated.sql"
go
:r "data migrationUpdate some other new table or column.sql"
go

这是正确的方法,还是有一些方法可以更好地配合SSDT及其版本跟踪,因此当针对已经处于更新版本的数据库运行SSDT发布时,这些脚本甚至都不会运行.我可以拥有自己的表来跟踪已经运行了哪些迁移,但是如果有一种标准的方法可以执行此操作,则不希望自己滚动.

静态或参考数据

一些数据库表包含我们称之为静态或参考数据的表,例如可能的时区,设置类型,货币,各种“类型”表等的列表.目前,我们通过为每个表提供单独的脚本来填充这些表,这些脚作为Post-Deployment脚本的一部分运行.每个静态数据脚本将所有“正确”的静态数据插入到表变量中,然后根据需要插入/更新/删除静态数据表.根据表,可能仅适用于插入或仅插入和删除但不更新现有记录.所以每个脚本看起来像这样：

-- table listing all the correct static data
declare @working_data table (...)

-- add all the static data that should exist into the working table
insert into @working_data (...) select null,null null where 1=0
union all select 'row1 col1 value','col2 value',etc...
union all select 'row2 col1 value',etc...
...

-- insert any missing records in the live table
insert into staticDataTableX (...)
select * from @working_data
where not exists ( select * from staticDataTableX
                   where [... primary key join on @working_data...] )

-- update any columns that should be updated
update staticDataTableX
set ...
from staticDataTableX
inner join @working_data on [... primary key join on @working_data...]

-- delete any records,if appropriate with this sort of static data
delete from staticDataTableX
where not exists ( select * from staticDataTableX
                   where [... primary key join on @working_data...] )

然后我的Post-Deployment脚本有一个这样的部分：

-- static data. each script adds any missing static/reference data:
:r "static_datasettings.sql"
go
:r "static_dataother_static_data.sql"
go
:r "static_datamore_static_data.sql"
go

有没有更好或更传统的方法来构建这样的静态数据脚本作为SSDT项目的一部分？

解决方法

要添加扩展属性：

EXEC sys.sp_addextendedproperty 
@name = N'EP_Charge_HasComments',@value = N'Initialized',@level0type = N'SCHEMA',@level0name = dbo,@level1type = N'TABLE',@level1name = Charge,@level2type = N'COLUMN',@level2name = HasComments;

要检查扩展属性：

SELECT objtype,objname,name,value
FROM fn_listextendedproperty (NULL,'SCHEMA','dbo','TABLE','Charge','COLUMN','HasComments');

有关参考数据,请尝试使用MERGE.它比您正在使用的三组查询更清晰.

MERGE INTO staticDataTableX AS Target
USING (
VALUES  
        ('row1_UniqueID','row1_col1_value','col2_value'),('row2_UniqueID','row2_col1_value',('row3_UniqueID','row3_col1_value',('row4_UniqueID','row4_col1_value','col2_value')


    ) AS Source (TableXID,col1,col2)
        ON Target.TableXID = Source.TableXID
WHEN MATCHED THEN
   UPDATE SET 
        Target.col1 = Source.col1,Target.col2 = Source.col2  

WHEN NOT MATCHED BY TARGET THEN
   INSERT (TableXID,col2)
   VALUES (Source.TableXID,Source.col1,Source.col2)

WHEN NOT MATCHED BY SOURCE THEN
    DELETE;

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!