sql - 从现有数据填充 Visual Studio 数据库项目数据加载脚本

Question

所以，一段时间以来我一直在寻找解决方案，并想出了什么样的作品......但我不禁觉得一定有更优雅的东西。

我正在寻找的是能够从填充的数据库中提取现有数据并将该数据合并到加载脚本中。数据库架构和配置数据将多次推出，并且会随着开发的继续而改变，因此能够从现有数据而不是脚本中保存的静态数据重建配置数据非常重要。

这是我拼凑的:

create procedure #dump (
    @TableName      varchar(128)
    )
as

set nocount on
set rowcount 0

declare @template varchar(max)
set @template = 'SET IDENTITY_INSERT [dbo].[' + @TableName + '] ON

MERGE INTO [dbo].[' + @TableName + '] AS [Target]
USING
(
VALUES
{vals}
)
AS [Source] ({fields})
ON [Target].[{pk}] = [Source].[{pk}]
WHEN MATCHED THEN UPDATE SET
{upds}
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
{fields}
)
VALUES
(
{fields}
);

SET IDENTITY_INSERT [dbo].[' + @TableName + '] OFF
--------------------------------------------------
'

declare @pk varchar(max) = ''
declare @vals varchar(max) = '/*
set concat_null_yields_null off
select ''' + '(' + ''' + replace(replace({casts} + '') ,'', '',,'', '', null,''), ''' + ',)' + ''', ''' + ',null)' + ''') from [' + @TableName + ']
*/'
declare @casts varchar(max) = ''
declare @fields varchar(max) = ''
declare @upds varchar(max) = ''
declare @inserts varchar(max) = ''

set @pk = SUBSTRING(@TableName, 1, len(@TableName) - 1) + 'ID'

declare cur_flds
cursor for select c.name, c.type
from syscolumns c
where c.id = object_id(@TableName)
order by c.colid

declare @fn varchar(max)
declare @ft int

open cur_flds
fetch next from cur_flds into @fn, @ft
while @@FETCH_STATUS = 0
    begin
        if len(@fields) > 0
            set @fields = @fields + ', '
        set @fields = @fields + '[' + @fn + ']'

        if len(@casts) > 0
            set @casts = @casts + ' + ' + ''','' + '
        if @ft in(56,55,50,38,48)
            set @casts = @casts + 'cast([' + @fn + '] as varchar)'
        else if @ft = 111
            set @casts = @casts + ''''''''' + ' + 'cast([' + @fn + '] as varchar) + ' + ''''''''''
        else
            set @casts = @casts + ''''''''' + ' + 'replace([' + @fn + '], ''''''''' + ', ' + ''''''''''''') + '''''''''


        if @fn != @pk
            begin
                if len(@upds) > 0
                    set @upds = @upds + ', '
                set @upds = @upds + '[Target].[' + @fn + '] = [Source].[' + @fn + ']'
            end

        fetch next from cur_flds into @fn, @ft
    end

close cur_flds
deallocate cur_flds

set @vals = REPLACE(@vals, '{casts}', @casts)

set @template = REPLACE(@template, '{pk}', @pk)
set @template = REPLACE(@template, '{vals}', @vals)
set @template = REPLACE(@template, '{fields}', @fields)
set @template = REPLACE(@template, '{upds}', @upds)
set @template = REPLACE(@template, '{inserts}', @inserts)

print @template

go


exec #dump 'ActionItemSystems'

drop proc #dump

最终得到的输出是:

SET IDENTITY_INSERT [dbo].[ActionItemSystems] ON

MERGE INTO [dbo].[ActionItemSystems] AS [Target]
USING
(
VALUES
/*
set concat_null_yields_null off
select '(' + replace(replace(cast([ActionItemSystemID] as varchar) + ',' + '''' + replace([ActionItemSystemName], '''', '''''') + '''' + ') ,', ',,', ', null,'), ',)', ',null)') from [ActionItemSystems]
*/
)
AS [Source] ([ActionItemSystemID], [ActionItemSystemName])
ON [Target].[ActionItemSystemID] = [Source].[ActionItemSystemID]
WHEN MATCHED THEN UPDATE SET
[Target].[ActionItemSystemName] = [Source].[ActionItemSystemName]
WHEN NOT MATCHED BY TARGET THEN
INSERT
(
[ActionItemSystemID], [ActionItemSystemName]
)
VALUES
(
[ActionItemSystemID], [ActionItemSystemName]
);

SET IDENTITY_INSERT [dbo].[ActionItemSystems] OFF

从这一点来看，我可以采取注释掉的位

set concat_null_yields_null off
select '(' + replace(replace(cast([ActionItemSystemID] as varchar) + ',' + '''' + replace([ActionItemSystemName], '''', '''''') + '''' + ') ,', ',,', ', null,'), ',)', ',null)') from [ActionItemSystems]

执行它，得到如下输出:

(33,'7111-5 -Upstream/Seed Lab') ,
(32,'7301-Seed Lab') ,
(30,'7807 UFDF') ,
(14,'BAS Panel Upgrade') ,
(1,'Clean Steam') ,
(13,'DCS') ,
(2,'HWFI') ,
(3,'MCS') ,
(12,'MES') ,
(31,'Seed Lab') ,
(18,'UCS WRO') ,
(34,'Upstream Seed Lab') ,
(29,'Viral Filtration') ,

然后可以将其合并(没有最后的逗号)到脚本中。

现在，这个解决方案起作用了，但它很脆弱。它取决于各种假设(例如，表名将具有表名的主键 - 尾随“s”和加号 ID)可能并非对每个解决方案都适用。它还需要剪切和粘贴，并在表结构发生变化时从头开始重新运行。

这可能是相当多的背景知识……我之所以分享部分背景知识，是因为我找不到任何类似的东西，并且认为有人可能会从中受益。但是，我还是回到了我真正的问题，也就是说:为 VS 数据库项目生成这种脚本的工具在哪里？确实应该有一些东西 - 可以考虑主键的东西，可以生成完整的东西，等等。

Answer 1

您可以尝试使用此过程生成 MERGE 语句: https://github.com/readyroll/generate-sql-merge它是您现有版本的更高级版本。