dbo.usp_generate_merge - P

type: P ( stored procedure ), modify_date: 2021-11-24 13:32:17

RepoObject_guid: FCAFBA8C-AD72-EB11-84E3-A81E8446D5B0

Description

Examples

Entity Diagram

entity-dbo.usp_generate_merge

References

Object Reference Diagram - 1 1

entity_1_1_objectref-dbo.usp_generate_merge

Object Reference Diagram - Referenced - 30 0

entity_30_0_objectref-dbo.usp_generate_merge

Object Reference Diagram - Referencing - 0 30

entity_0_30_objectref-dbo.usp_generate_merge

sql_modules_definition

dbo.usp_generate_merge - P script
/*
create the procedure sp_generate_merge in master database
details: https://github.com/readyroll/generate-sql-merge

issue in orignal procedure with sql_variant content!

this will create sql statements to merge data into a target table
these scrpits can be included in database projects to use in post deployment scripts in DACPAC

https://documentation.red-gate.com/rr1/key-concepts/data-population/static-data#StaticData-offline

ATTENTION:
for sql_variant type the procedure generates wrong code:
[repo].[Parameter]
[property].[RepoObjectColumnProperty]
[property].[RepoObjectProperty]

*/

CREATE Procedure dbo.usp_generate_merge
As

--issues with sql_variant
Exec sp_generate_merge
    @table_name = 'Parameter'
  , @schema = 'repo'
  , @debug_mode = 1;

Exec sp_generate_merge
    @table_name = 'GeneratorUsp'
  , @schema = 'repo'
  , @debug_mode = 1;
Exec sp_generate_merge
    @table_name = 'GeneratorUspParameter'
  , @schema = 'repo'
  , @debug_mode = 1;
Exec sp_generate_merge
    @table_name = 'GeneratorUspStep'
  , @schema = 'repo'
  , @debug_mode = 1;

--todo: store and get all columns in extended properties
/*
TITLE: Microsoft SQL Server Management Studio
------------------------------

Unable to show XML. The following error happened:
Unexpected end of file while parsing PI has occurred. Line 162, position 154777.

One solution is to increase the number of characters retrieved from the server for XML data. To change this setting, on the Tools menu, click Options.

------------------------------
BUTTONS:

OK
------------------------------


Menu > Tools > Options > Query Results > Results to Grid > XML Data
default is 2 MB, set to unlimited

But this can crash SSMS

*/
Exec sp_generate_merge
    @table_name = 'RepoObject'
  , @schema = 'repo'
  , @debug_mode = 1;
--todo: store and get all columns in extended properties
Exec sp_generate_merge
    @table_name = 'RepoObject_persistence'
  , @schema = 'repo'
  , @debug_mode = 1;
-- RepoObject_SqlModules can be easy restored using the python script SqlParser.py
Exec sp_generate_merge
    @table_name = 'RepoObject_SqlModules'
  , @schema = 'repo'
  , @debug_mode = 1;

----not required, get properties using [repo].[usp_sync_ExtendedProperties_Sys2Repo_InsertUpdate]
----issues with sql_variant
--EXEC sp_generate_merge @table_name = 'RepoObjectProperty', @schema = 'property', @debug_mode = 1

Exec sp_generate_merge
    @table_name = 'RepoObjectColumn'
  , @schema = 'repo'
  , @debug_mode = 1;
----not required, get properties using [repo].[usp_sync_ExtendedProperties_Sys2Repo_InsertUpdate]
----issues with sql_variant
--EXEC sp_generate_merge @table_name = 'RepoObjectColumnProperty', @schema = 'property', @debug_mode = 1

----currently only the SqlParser data is used
--EXEC sp_generate_merge @table_name = 'RepoObjectSource_FirstResultSet', @schema = 'repo', @debug_mode = 1
--EXEC sp_generate_merge @table_name = 'RepoObjectSource_QueryPlan', @schema = 'repo', @debug_mode = 1

Exec sp_generate_merge
    @table_name = 'Index_virtual'
  , @schema = 'repo'
  , @debug_mode = 1;
Exec sp_generate_merge
    @table_name = 'IndexColumn_virtual'
  , @schema = 'repo'
  , @debug_mode = 1;
Exec sp_generate_merge
    @table_name = 'Index_Settings'
  , @schema = 'repo'
  , @debug_mode = 1;

Exec sp_generate_merge
    @table_name = 'ProcedureInstance'
  , @schema = 'repo'
  , @debug_mode = 1;
Exec sp_generate_merge
    @table_name = 'ProcedureInstanceDependency'
  , @schema = 'repo'
  , @debug_mode = 1;
Exec sp_generate_merge
    @table_name = 'Workflow'
  , @schema = 'repo'
  , @debug_mode = 1;
Exec sp_generate_merge
    @table_name = 'WorkflowStep'
  , @schema = 'repo'
  , @debug_mode = 1;