从现有数据中填充 Visual Studio 数据库项目数据加载脚本 [英] Population of Visual Studio Database Project data-loading scripts from existing data

问题描述

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

So, I've been hunting for a solution for this for awhile and have come up with what sort of works ... but I can't help feeling that there must be something more elegant.

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

What I'm looking for is to be able to extract existing data from a populated database & incorporate that data into loading scripts. The database schema & configuration data will be rolled out multiple times, and will change as development continues, so it's important to be able to rebuild the configuration data from existing data, rather than from static data kept in scripts.

这是我拼凑起来的:

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

这最终给了我以下输出:

That ends up giving me output of:

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

从这一点来说，我可以拿注释掉的部分

From this point, I can take the commented-out bit

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

执行那个，得到如下输出:

execute that, and get output like:

(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') ,

然后可以将其合并(没有最后一个逗号)到脚本中.

which can then be incorporated (sans the final comma) into the script.

现在，这个解决方案起作用了，但它很脆弱.这取决于各种假设(例如，表名称将具有表名称的主键 - 尾随 's' 和加 ID)，这些假设可能不适用于每个解决方案.它还需要切割&粘贴，并在表结构更改时从头开始重新运行.

Now, this solution functions, but it's fragile. It depends on various assumptions (e.g., that the Table Name will have a Primary Key of Table Name - trailing 's' and plus ID) that may not hold true for every solution. It also requires cutting & pasting, and rerunning from the start when the table structures change.

这可能是相当多的背景......我分享了一部分，因为我在那里找不到任何类似的东西&认为有人可能会从中受益.但是，我仍然回到我真正的问题，也就是说:为 VS 数据库项目生成这种脚本的工具在哪里?真的应该有一些东西 - 一些东西会考虑主键是什么，会生成整个东西，等等.

This is probably quite a lot of background ... which I'm partly sharing because I couldn't find anything similar out there & thought that somebody might benefit from this. However, I still come back to my real question, which is to say: where's the tool to generate this kind of script for VS Database Projects? There really should be something - something that would take into account whatever the primary key is, that would generate the thing entire, etc.

推荐答案

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

You can try with this procedure for generating MERGE statements: https://github.com/readyroll/generate-sql-merge It is more advanced version of what you already have.

这篇关于从现有数据中填充 Visual Studio 数据库项目数据加载脚本的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！