Population of Visual Studio Database Database Loading Data from Existing Data

So, I've been looking for a solution to this for a while and came up with what works ... but I can't help but feel like there must be something more elegant.

I'm looking to be able to fetch existing data from a populated database and include that data in a load script. The database schema and configuration data will be deployed multiple times and will change as development continues, so it is important to be able to rebuild the configuration data from existing data rather than from static data stored in scripts.

Here's what I put together:

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

      

        
        
        
      

    

This gives me the output:

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 now on I can take the commented bit

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

      

        
        
        
      

    

do this 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 included (no trailing comma) in the script.

This solution is now functional, but fragile. It depends on various assumptions (for example, that the table name will have the primary key of the table name - end plus ID), which may not be true for every solution. It also requires cut and paste and rerun from the beginning when the table structure changes.

This is probably quite a bit of background ... which I partly share because I couldn't find anything like it and thought someone might benefit from it. However, I'll still get back to my real question, which is: where is the tool to generate this kind of script for VS Database Projects? There really should be something that will account for any primary key that will generate the whole thing, etc.