search

dnlnln / generate-sql-merge

Generate SQL MERGE statements with Table data
MIT License
337 stars 151 forks source link

Unique variable names in generated sql when using @include_rowsaffected #93

Closed jbelanger closed 1 year ago

jbelanger commented 1 year ago

The proposed changes address an issue that arises when multiple merge statements, generated by this script, are combined into a single script file. I personally found about this issue when making a DB release with SSDT, the postdeployment script that contains all of the lookup data failed to run:

The variable name '@mergeOutput' has already been declared. Variable names must be unique within a query batch or stored procedure.

The variable names, such as mergeOutput and mergeCount, gets declared multiple times. This change only makes the variable names unique by appending the table's object id.

dnlnln commented 1 year ago

Thanks for your contribution! I'll have a proper look through it shortly

dnlnln commented 1 year ago

Just fyi I have tweaked the behaviour of the output variable naming: now, sp_generate_merge will only append a variable name suffix when a naming collision is actually possible. This maintains consistency of the output for those who need to re-generate their MERGE statements in source control. See this commit for details; any concerns let me know, thanks!