dnlnln / generate-sql-merge Goto Github PK
View Code? Open in Web Editor NEWGenerate SQL MERGE statements with Table data
License: MIT License
Generate SQL MERGE statements with Table data
License: MIT License
Hello,
I'm trying to export certain columns from a join, but since a column is in more than one table, I'm using table aliases.
EXEC sp_generate_merge 'actionXRole', @disable_constraints = 1, @delete_if_not_matched = 0, @nologo = 1, @include_use_db = 0, @schema = 'dbo', @debug_mode = 1, @ommit_generated_always_cols = 1, @cols_to_include = "'ActionXSecurityRoleid','asr.actionid','securityroleid'", @from ='from dbo.actionXRole asr inner join dbo.action a on a.actionid = asr.actionid where a.name = ''CharismaManualDisbursement'''
I've specified @cols_to_include with an alias for asr.actionid but it's excluded from the end result.
I've used @debug_mode to see what's happening and the debug made me realize that the alias is not properly placed.
SELECT ' ' + CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) = 1 THEN ' ' ELSE ',' END + '('+ COALESCE('N''' + REPLACE(CONVERT(char(36),RTRIM([ActionXSecurityRoleid])),'''','''''')+'''','NULL')+',' + COALESCE('N''' + REPLACE(CONVERT(char(36),RTRIM([securityroleid])),'''','''''')+'''','NULL')+',' + COALESCE('N''' + REPLACE(CONVERT(char(36),RTRIM(asr.[actionid])),'''','''''')+'''','NULL') +')' from EbsMetadata.actionXSecurityRole asr inner join EbsMetadata.action a on a.actionid = asr.actionid where a.name = 'CharismaManualDisbursement'
With the alias like RTRIM(asr.[actionid])) the query works, but if I try to put it inside like RTRIM([asr.actionid])) it's not working.
It's there a parameter that can be used or this script it's not working with table aliases?
Thanks,
Alex
For sql_variant datatype It would be required to check the basetype using for example sql_variant_property([Parameter_value],'BaseType')
and to generate the correct values based on the BaseType.
If you accept Pull requests I could adapt the procedure. But I see that other pull requests are open for long time and it is not clear if pull requests would be accepted or not.
Here an example, this is the table, containing 2 columns of type sql_variant:
CREATE TABLE [repo].[Parameter](
[Parameter_name] [varchar](100) NOT NULL,
[sub_Parameter] [nvarchar](128) NOT NULL,
[Parameter_desciption] [nvarchar](1000) NULL,
[Parameter_default_value] [sql_variant] NULL,
[Parameter_value] [sql_variant] NULL,
[Parameter_value__result_nvarchar] AS (TRY_CAST(coalesce([Parameter_value],[Parameter_default_value]) AS [nvarchar](4000))),
[Parameter_value__result_int] AS (TRY_CAST(coalesce([Parameter_value],[Parameter_default_value]) AS [int])),
CONSTRAINT [PK_Parameter] PRIMARY KEY CLUSTERED
(
[Parameter_name] ASC,
[sub_Parameter] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
and the generated scripts are wrong for sql_variant data:
/*****START OF DEBUG INFORMATION*****
The primary key column list:
[Parameter_name], [sub_Parameter]
The INSERT column list:
[Parameter_name],[sub_Parameter],[Parameter_desciption],[Parameter_default_value],[Parameter_value]
The UPDATE column list:
[Target].[Parameter_desciption] = [Source].[Parameter_desciption],
[Target].[Parameter_default_value] = [Source].[Parameter_default_value],
[Target].[Parameter_value] = [Source].[Parameter_value]
The SELECT statement executed to generate the MERGE:
SELECT ' ' + CASE WHEN ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) = 1 THEN ' ' ELSE ',' END + '('+ COALESCE('N''' + REPLACE([Parameter_name],'''','''''')+'''','NULL')+',' + COALESCE('N''' + REPLACE([sub_Parameter],'''','''''')+'''','NULL')+',' + COALESCE('N''' + REPLACE([Parameter_desciption],'''','''''')+'''','NULL')+',' + COALESCE(LTRIM(RTRIM(CONVERT(char, [Parameter_default_value]))),'NULL')+',' + COALESCE(LTRIM(RTRIM(CONVERT(char, [Parameter_value]))),'NULL')+')' FROM [repo].[Parameter] (NOLOCK) ORDER BY [Parameter_name], [sub_Parameter]
*****END OF DEBUG INFORMATION*****/
USE [dhw_self]
GO
--MERGE generated by 'sp_generate_merge' stored procedure
--Originally by Vyas (http://vyaskn.tripod.com/code): sp_generate_inserts (build 22)
--Adapted for SQL Server 2008+ by Daniel Nolan (https://twitter.com/dnlnln)
SET NOCOUNT ON
MERGE INTO [repo].[Parameter] AS [Target]
USING (VALUES
(N'',N'',NULL,NULL,NULL)
,(N'DUMMY',N'',N'dummy parameter, data type SQL_Variant',,NULL)
,(N'dwh_database_name',N'',N'The database name must be the same as the one used in the synonyms',master,dhw_self)
,(N'Hist_Table_name_suffix',N'',N'default suffix for historization table which will be added to historized object name',_hist,NULL)
,(N'Hist_Table_schema',N'',N'default: NULL - The historization table uses the same schema as the table to be historized. otherwise the given schema is used',NULL,NULL)
,(N'Hist_ValidFrom_column_name',N'',N'default column name for column - datetime2 GENERATED ALWAYS AS ROW START',ValidFrom,NULL)
,(N'Hist_ValidTo_column_name',N'',N'default column name for column - datetime2 GENERATED ALWAYS AS ROW END',ValidTo,NULL)
,(N'Inheritance_StringAggSeparatorSql_column',N'',N'if NULL then only one source is used for inheritance; if not NULL then STRING_AGG( expression, separator ) is used to aggregate all sources. Content is interpreted as TSQL. Good values are ''CHAR(13)+CHAR(10)'' or '''';''''',NULL,NULL)
,(N'Inheritance_StringAggSeparatorSql_column',N'ReferencedObjectColumnList',N'if NULL then only one source is used for inheritance; if not NULL then STRING_AGG( expression, separator ) is used to aggregate all sources. Content is interpreted as TSQL. Good values are ''CHAR(13)+CHAR(10)'' or '''';''''',
,NULL)
,(N'Inheritance_StringAggSeparatorSql_object',N'',N'if NULL then only one source is used for inheritance; if not NULL then STRING_AGG( expression, separator ) is used to aggregate all sources. Content is interpreted as TSQL. Good values are ''CHAR(13)+CHAR(10)'' or '''';''''',NULL,NULL)
,(N'Inheritance_StringAggSeparatorSql_object',N'ReferencedObjectList',N'if NULL then only one source is used for inheritance; if not NULL then STRING_AGG( expression, separator ) is used to aggregate all sources. Content is interpreted as TSQL. Good values are ''CHAR(13)+CHAR(10)'' or '''';''''',
,NULL)
,(N'InheritanceDefinition_column',N'',N'CONCAT arguments to be used with some specific values in [repo].[InheritanceType], for example: ''[RepoObject_name],CHAR(13),CHAR(10),EineNochZuDefinierendeFunktion(''MS_Description'')''',NULL,NULL)
,(N'InheritanceDefinition_column',N'MS_Description',N'CONCAT arguments to be used with some specific values in [repo].[InheritanceType], for example: ''[RepoObject_name],CHAR(13),CHAR(10),EineNochZuDefinierendeFunktion(''MS_Description'')''',CAST(COALESCE(referencing.[Rep,NULL)
,(N'InheritanceDefinition_column',N'ReferencedObjectColumnList',N'CONCAT arguments to be used with some specific values in [repo].[InheritanceType], for example: ''[RepoObject_name],CHAR(13),CHAR(10),EineNochZuDefinierendeFunktion(''MS_Description'')''',referenced.[RepoObjectColumn_f,NULL)
,(N'InheritanceDefinition_object',N'',N'CONCAT arguments to be used with some specific values in [repo].[InheritanceType], for example: ''[RepoObject_name],CHAR(13),CHAR(10),EineNochZuDefinierendeFunktion(''MS_Description'')''',NULL,NULL)
,(N'InheritanceDefinition_object',N'MS_Description',N'CONCAT arguments to be used with some specific values in [repo].[InheritanceType], for example: ''[RepoObject_name],CHAR(13),CHAR(10),EineNochZuDefinierendeFunktion(''MS_Description'')''',NULL,NULL)
,(N'InheritanceDefinition_object',N'ReferencedObjectList',N'CONCAT arguments to be used with some specific values in [repo].[InheritanceType], for example: ''[RepoObject_name],CHAR(13),CHAR(10),EineNochZuDefinierendeFunktion(''MS_Description'')''',referenced.[RepoObject_fullnam,NULL)
,(N'InheritanceType_column',N'',N'TINYINT; InheritanceType for column: possible values in [repo].[InheritanceType]',0,NULL)
,(N'InheritanceType_column',N'MS_Description',N'TINYINT; InheritanceType for column: possible values in [repo].[InheritanceType]',0,12)
,(N'InheritanceType_column',N'ReferencedObjectColumnList',N'TINYINT; InheritanceType for object: possible values in [repo].[InheritanceType]',14,NULL)
,(N'InheritanceType_object',N'',N'TINYINT; InheritanceType for object: possible values in [repo].[InheritanceType]',0,NULL)
,(N'InheritanceType_object',N'MS_Description',N'TINYINT; InheritanceType for object: possible values in [repo].[InheritanceType]',0,NULL)
,(N'InheritanceType_object',N'ReferencedObjectList',N'TINYINT; InheritanceType for object: possible values in [repo].[InheritanceType]',14,NULL)
,(N'main enable usp_RepoObject_update_SysObjectQueryPlan',N'',N'execute (or not) usp_RepoObject_update_SysObjectQueryPlan',0,NULL)
,(N'main enable usp_RepoObjectSource_FirstResultSet',N'',N'execute (or not) usp_RepoObjectSource_FirstResultSet',0,NULL)
,(N'main enable usp_RepoObjectSource_QueryPlan',N'',N'execute (or not) usp_RepoObjectSource_QueryPlan',0,NULL)
,(N'persistence_name_suffix',N'',N'default suffix for persistence table which will be added to source object name',_T,NULL)
,(N'RepoObjectColumn_column_id_OrderBy',N'',N'used in repo.usp_RepoObjectColumn__update_RepoObjectColumn_column_id to define the order of columns',
ISNULL([ic].[index_column_id,NULL)
) AS [Source] ([Parameter_name],[sub_Parameter],[Parameter_desciption],[Parameter_default_value],[Parameter_value])
ON ([Target].[Parameter_name] = [Source].[Parameter_name] AND [Target].[sub_Parameter] = [Source].[sub_Parameter])
WHEN MATCHED AND (
NULLIF([Source].[Parameter_desciption], [Target].[Parameter_desciption]) IS NOT NULL OR NULLIF([Target].[Parameter_desciption], [Source].[Parameter_desciption]) IS NOT NULL OR
NULLIF([Source].[Parameter_default_value], [Target].[Parameter_default_value]) IS NOT NULL OR NULLIF([Target].[Parameter_default_value], [Source].[Parameter_default_value]) IS NOT NULL OR
NULLIF([Source].[Parameter_value], [Target].[Parameter_value]) IS NOT NULL OR NULLIF([Target].[Parameter_value], [Source].[Parameter_value]) IS NOT NULL) THEN
UPDATE SET
[Target].[Parameter_desciption] = [Source].[Parameter_desciption],
[Target].[Parameter_default_value] = [Source].[Parameter_default_value],
[Target].[Parameter_value] = [Source].[Parameter_value]
WHEN NOT MATCHED BY TARGET THEN
INSERT([Parameter_name],[sub_Parameter],[Parameter_desciption],[Parameter_default_value],[Parameter_value])
VALUES([Source].[Parameter_name],[Source].[sub_Parameter],[Source].[Parameter_desciption],[Source].[Parameter_default_value],[Source].[Parameter_value])
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
DECLARE @mergeError int
, @mergeCount int
SELECT @mergeError = @@ERROR, @mergeCount = @@ROWCOUNT
IF @mergeError != 0
BEGIN
PRINT 'ERROR OCCURRED IN MERGE FOR [repo].[Parameter]. Rows affected: ' + CAST(@mergeCount AS VARCHAR(100)); -- SQL should always return zero rows affected
END
ELSE
BEGIN
PRINT '[repo].[Parameter] rows affected by MERGE: ' + CAST(@mergeCount AS VARCHAR(100));
END
GO
SET NOCOUNT OFF
GO
´´´
Hello,
Thanks for the great job!
Would it be possible to consider the option to generate SQL MERGE statements for multiple tables?
For instance:
EXEC [sp_generate_merge] 'AddressType;SecondTable;ThirdTable', @Schema='Person'
Thanks!
Originally reported by @Dunnymeister in PR #26
Running sp_generate_merge on a table where there are no rows, or with a @from filter which returns no rows, created invalid SQL.
Execute the following (AdventureWorks.dbo.ErrorLog
table should have zero rows):
EXEC [AdventureWorks2014]..[sp_generate_merge] @table_name = 'ErrorLog', @schema = 'dbo'
This generates the following T-SQL:
USE AdventureWorks2014
GO
SET NOCOUNT ON
SET IDENTITY_INSERT [dbo].[ErrorLog] ON
MERGE INTO [dbo].[ErrorLog] AS Target
USING (VALUES
) AS Source ([ErrorLogID],[ErrorTime],[UserName],[ErrorNumber],[ErrorSeverity],[ErrorState],[ErrorProcedure],[ErrorLine],[ErrorMessage])
ON (Target.[ErrorLogID] = Source.[ErrorLogID])
WHEN MATCHED AND (
NULLIF(Source.[ErrorTime], Target.[ErrorTime]) IS NOT NULL OR NULLIF(Target.[ErrorTime], Source.[ErrorTime]) IS NOT NULL OR
NULLIF(Source.[UserName], Target.[UserName]) IS NOT NULL OR NULLIF(Target.[UserName], Source.[UserName]) IS NOT NULL OR
NULLIF(Source.[ErrorNumber], Target.[ErrorNumber]) IS NOT NULL OR NULLIF(Target.[ErrorNumber], Source.[ErrorNumber]) IS NOT NULL OR
NULLIF(Source.[ErrorSeverity], Target.[ErrorSeverity]) IS NOT NULL OR NULLIF(Target.[ErrorSeverity], Source.[ErrorSeverity]) IS NOT NULL OR
NULLIF(Source.[ErrorState], Target.[ErrorState]) IS NOT NULL OR NULLIF(Target.[ErrorState], Source.[ErrorState]) IS NOT NULL OR
NULLIF(Source.[ErrorProcedure], Target.[ErrorProcedure]) IS NOT NULL OR NULLIF(Target.[ErrorProcedure], Source.[ErrorProcedure]) IS NOT NULL OR
NULLIF(Source.[ErrorLine], Target.[ErrorLine]) IS NOT NULL OR NULLIF(Target.[ErrorLine], Source.[ErrorLine]) IS NOT NULL OR
NULLIF(Source.[ErrorMessage], Target.[ErrorMessage]) IS NOT NULL OR NULLIF(Target.[ErrorMessage], Source.[ErrorMessage]) IS NOT NULL) THEN
UPDATE SET
[ErrorTime] = Source.[ErrorTime],
[UserName] = Source.[UserName],
[ErrorNumber] = Source.[ErrorNumber],
[ErrorSeverity] = Source.[ErrorSeverity],
[ErrorState] = Source.[ErrorState],
[ErrorProcedure] = Source.[ErrorProcedure],
[ErrorLine] = Source.[ErrorLine],
[ErrorMessage] = Source.[ErrorMessage]
WHEN NOT MATCHED BY TARGET THEN
INSERT([ErrorLogID],[ErrorTime],[UserName],[ErrorNumber],[ErrorSeverity],[ErrorState],[ErrorProcedure],[ErrorLine],[ErrorMessage])
VALUES(Source.[ErrorLogID],Source.[ErrorTime],Source.[UserName],Source.[ErrorNumber],Source.[ErrorSeverity],Source.[ErrorState],Source.[ErrorProcedure],Source.[ErrorLine],Source.[ErrorMessage])
WHEN NOT MATCHED BY SOURCE THEN
DELETE
;
GO
DECLARE @mergeError int
, @mergeCount int
SELECT @mergeError = @@ERROR, @mergeCount = @@ROWCOUNT
IF @mergeError != 0
BEGIN
PRINT 'ERROR OCCURRED IN MERGE FOR [dbo].[ErrorLog]. Rows affected: ' + CAST(@mergeCount AS VARCHAR(100)); -- SQL should always return zero rows affected
END
ELSE
BEGIN
PRINT '[dbo].[ErrorLog] rows affected by MERGE: ' + CAST(@mergeCount AS VARCHAR(100));
END
GO
SET IDENTITY_INSERT [dbo].[ErrorLog] OFF
GO
SET NOCOUNT OFF
GO
Execute the generated T-SQL to produce the following error:
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near ')'.
ERROR OCCURRED IN MERGE FOR [dbo].[ErrorLog]. Rows affected: 0
uniqueidentifier converted to 255 char string
Just noticed @cfbarbero has pull request for the same issue
Originally reported by @Dunnymeister in PR #25
TIMESTAMP column without a default would cause errors when attempting to apply the DEFAULT.
Even though a default isn't present on a TIMESTAMP column, SQL Server handles updating the value behind the scenes. Since there's no need to use DEFAULT, and because SQL Server does not allow INSERT or UPDATE of TIMESTAMP/ROWVERSION columns with a specified value, such columns should probably be removed altogether from the generated MERGE statements.
Additionally, removing such columns from the tool would render the @include_timestamp
parameter obsolete.
SQL Server 2016 Express
Msg 9420, Level 16, State 1, Procedure sp_generate_merge, Line 540 [Batch Start Line 0] XML parsing: line 1, character 210, illegal xml character
I hesitate to suggest a change here, but there's an input variable "ommit_computed_cols" which should likely be "omit" with a single "m". Not sure I'd change it, as it's likely used by somebody?
I am looking to use this however I am unable to find information on the license.
Is it possible to add a license?
Thanks very much for providing this script. I did notice that it was generating sql for computed columns though.
If I use more than 128 chars in @cols_to_exclude I get:
The identifier that starts with ''InterestFreeDays','PaymentAccountID','MinDaysBeforeInterest','InterestPriceGroupID','InterestFromHoldDueDate','MinInterestAmoun' is too long. Maximum length is 128.
Issue:
The merge statement becomes too complex for MS SQL Server when dealing with bigger tables.
Suggestion:
Enable batch processing (@batchsize parameter) that does a merge per given batch size.
the 8/7/23 file is erroring ❌ on install for me:
Using Master database
Checking for the existence of this procedure
Procedure already exists. So, dropping it
Msg 6850, Level 16, State 1, Procedure sp_generate_merge, Line 964 [Batch Start Line 14]
Column name 'processing - instruction(x)' contains an invalid XML identifier as required by FOR XML; ' '(0x0020) is the first character at fault.
Created the procedure
sp_MS_marksystemobject: Invalid object name 'sp_generate_merge'
Granting EXECUTE permission on sp_generate_merge to all users
Msg 15151, Level 16, State 1, Line 1005
Cannot find the object 'sp_generate_merge', because it does not exist or you do not have permission.
Done
Completion time: 2023-08-13T18:46:04.3948666-04:00
I was able to successfully install this version from 7/31/23 ✅.
reference: history for master.dbo.sp_generate_merge.sql
thanks for sharing/creating/maintaining a great tool! 🤝
Hi,
First of all thanks for the work you have done on this.
I've been using this sometime but with the newer version, it seems we lost the column names and tabbing depending on value lengths. Was this removed in recent versions?
This script looks awesome. How would you support creating MERGE scripts for a SQL environment that has row limitations on scripts?
Ex: table has 5,000 elements but the SQL Server only allows scripts to run over 1,000 elements at a time. Therefore the generate-sql-merge takes a parameter that says 1000 and makes 5 MERGE statements.
I know I could just run the script and do the split by 1000 by hand, so if it's too much trouble let me know.
It looks like the sp_generate_merge procedure can not be created inside an azure sql server database.
Checking for the existence of this procedure Procedure already exists. So, dropping it Created the procedure Meldung 2812, Ebene 16, Status 62, Zeile 901 Could not find stored procedure 'sp_MS_marksystemobject'. Granting EXECUTE permission on sp_generate_merge to all users Done
So the procedure depends on database objects that are only available with on-premise sql-server.
Is there a solution for that ?
Version: 12.0.2000.8
Hi,
i am using the addition of #21. For my use case of simple ETL merges it would be even better if the source could be a SQL query.
@sourceStmt='SELECT a, b FROM Staging.table'
regards
Both Local Temporary tables and Global Temporary tables created in the same session as the SP are not included in the INFORMATION_SCHEMA.TABLES table, and are thus not found.
When you try to find a temp table it returns a "User table or view not found.
Make sure you have SELECT permission on that table or view" error.
Steps to recreate:
Create a table called "[test].[testTable]" then run the following:
SELECT * INTO [test].[#test123] FROM [test].[testTable]
EXEC sp_generate_merge
@table_name = '#test123', --The table / view for which the MERGE statement will be generated using the existing data
@target_table = 'testTable', --Use this parameter to specify a different table name into which the data will be inserted/ updated / deleted
@from = NULL, --Use this parameter to filter the rows based on a filter condition(using WHERE)
@include_timestamp = 0, --Specify 1 for this parameter, if you want to include the TIMESTAMP / ROWVERSION column's data in the MERGE statement
@debug_mode = 0, --If @debug_mode is set to 1, the SQL statements constructed by this procedure will be printed for later examination
@schema = 'test', --Use this parameter if you are not the owner of the table
@ommit_images = 0, --Use this parameter to generate MERGE statement by omitting the 'image' columns
@ommit_identity = 0, --Use this parameter to ommit the identity columns
@top = NULL, --Use this parameter to generate a MERGE statement only for the TOP n rows
@cols_to_include = NULL, --List of columns to be included in the MERGE statement
@cols_to_exclude = NULL, --List of columns to be excluded from the MERGE statement
@update_only_if_changed = 1, --When 1, only performs an UPDATE operation if an included column in a matched row has changed.
@delete_if_not_matched = 0, --When 1, deletes unmatched source rows from target, when 0 source rows will only be used to update existing rows or insert new.
@disable_constraints = 0, --When 1, disables foreign key constraints and enables them after the MERGE statement
@ommit_computed_cols = 0, --When 1, computed columns will not be included in the MERGE statement
@include_use_db = 1, --When 1, includes a USE[DatabaseName] statement at the beginning of the generated batch
@results_to_text = 0, --When 1, outputs results to grid/ messages window.When 0, outputs MERGE statement in an XML fragment.
@include_rowsaffected = 0, --When 1, a section is added to the end of the batch which outputs rows affected by the MERGE
@nologo = 1,
@batch_separator = ''-- Batch separator to use
Here is the output:
(201 rows affected)
Msg 50000, Level 16, State 1, Procedure sp_generate_merge, Line 216 [Batch Start Line 0]
User table or view not found.
Make sure you have SELECT permission on that table or view.
I found information about where the temp tables are stored here
Issue Description:
When attempting to add a 'Hashvalue' column on an Azure SQL Database called [database-with-a-hyphen], an error occurs if the database name contains hyphens. The error can be traced back to the following line:
Suggested Solution:
To mitigate this issue, it is recommended to enclose the database name using the QUOTENAME() function. This helps prevent syntax errors from arising during the execution of the SQL statement.
if you add a from parameter which includes a "order by" clause the commas are not set correctly. The first value row gets a comma and another row is missing the comma.
Problem occurs here: [https://github.com/readyroll/generate-sql-merge/blob/master/master.dbo.sp_generate_merge.sql#L480]
I am sure it's just a user error, but I can't figure out what I am doing wrong. This would be the perfect script.
I want to use it for a table I have (TRAVEL.dbo.AIRLINES) for example.
I execute (I am logged in as sa):
EXEC sp_generate_merge @schema = 'TRAVEL', @table_name ='dbo.AIRLINES'
Then I get this error.
Msg 50000, Level 16, State 1, Procedure sp_generate_merge, Line 216 [Batch Start Line 0]
User table or view not found.
Make sure you have SELECT permission on that table or view.
I would appreciate any help.
Hello, when using the stored procedure with cyrillic (and guess other unicode stuff), the nvarchar column values are replaced with ??.
Declaring the output variables as NVarchar should fix this.
Cheers.
The script has been very helpful to my project. Thank you very much.
I have run into one problem - When I run the script on a very large table, I get the following error:
Exception of type 'System.OutOfMemoryException' was thrown.
The particular table has 4 columns - 2 Int32 and 2 String type with about 1.7 million rows of data. Is it something that we can solve - or is it a inherent system limitation?
Hi,
Thanks for the great proc! I'm using it on a table with a large number of columns (76). I noticed though that the @Column_List_For_Check gets truncated in this case. I just change the declare to varchar(max) instead of varchar(8000) and it all looks good to me.
Thanks again!
How can you update with sensitive values?
For example
SET IDENTITY_INSERT [dbo].[LenderLevel] ON
Current value of Test ColumB is Other. It doesn't get updated with OTHER when this is run.
DECLARE @mergeOutput TABLE ( [DMLAction] VARCHAR(6) );
MERGE INTO [dbo].[Test] AS [Target]
USING (VALUES
(1,N'Branch',N'F',N'LENDERBR',N'BRANCH')
,(2,N'Mortgage Center',N'F',N'LENDERMC',N'MORTGAGE_CENTER')
,(3,N'Lender Head Office',N'F',N'LENDERHO',N'LENDER_HEAD_OFFICE')
,(4,N'Central Discharge Unit',N'F',NULL,N'CENTRAL_DISCHARGE_UNIT')
,(5,N'Region',N'F',NULL,N'REGION')
,(6,N'District',N'F',NULL,N'DISTRICT')
,(7,N'Other',N'F',NULL,N'OTHER')
) AS [Source] ([TestId],[Test],[IsDeleted],[ColumnA],[ColumnB])
ON ([Target].[TestId] = [Source].[TestId])
WHEN MATCHED AND (
NULLIF([Source].[Test], [Target].[Test]) IS NOT NULL OR NULLIF([Target].[Test], [Source].[Test]) IS NOT NULL OR
NULLIF([Source].[IsDeleted], [Target].[IsDeleted]) IS NOT NULL OR NULLIF([Target].[IsDeleted], [Source].[IsDeleted]) IS NOT NULL OR
NULLIF([Source].[ColumnA], [Target].[ColumnA]) IS NOT NULL OR NULLIF([Target].[ColumnA], [Source].[ColumnA]) IS NOT NULL OR
NULLIF([Source].[ColumnB], [Target].[ColumnB]) IS NOT NULL OR NULLIF([Target].[ColumnB], [Source].[ColumnB]) IS NOT NULL) THEN
UPDATE SET
[Target].[Test] = [Source].[Test],
[Target].[IsDeleted] = [Source].[IsDeleted],
[Target].[ColumnA] = [Source].[ColumnA],
[Target].[ColumnB] = [Source].[ColumnB]
WHEN NOT MATCHED BY TARGET THEN
INSERT([TestId],[Test],[IsDeleted],[ColumnA],[ColumnB])
VALUES([Source].[TestId],[Source].[Test],[Source].[IsDeleted],[Source].[ColumnA],[Source].[ColumnB])
I have dictionary tables that hold translated strings, ie. ComponentLoc with the following columns
ComponentId - foreign key to Components.Id
Name - holds translated name
Locale - foreign key to Locales.Id
When running SP
EXEC dbo.sp_generate_merge @schema = 'dbo', @table_name ='ComponentLoc'
I'm getting the following error:
Msg 50000, Level 16, State 1, Procedure sp_generate_merge, Line 452 [Batch Start Line 1]
Table has no primary keys. There should at least be one column in order to have a valid join.
The thing is that I can't add primary key to column Name; adding extra column just for the primary key doesn't make sense to me.
When attempting to generate a merge-statement on a table containing a column of the datetimeoffset datatype, the datetimeoffset-type is not recognized, resulting in datetimeoffset-values not being wrapped in single-quotes and generating a faulty script.
Steps to reproduce
CREATE TABLE tbl_datetimeoffset (id int IDENTITY PRIMARY KEY, dto datetimeoffset);
INSERT INTO tbl_datetimeoffset (dto) VALUES (SYSDATETIMEOFFSET());
EXEC sp_generate_merge 'tbl_datetimeoffset';
Result script
--MERGE generated by 'sp_generate_merge' stored procedure, Version 0.93
--Originally by Vyas (http://vyaskn.tripod.com): sp_generate_inserts (build 22)
--Adapted for SQL Server 2008/2012 by Daniel Nolan (http://danere.com)
SET NOCOUNT ON
SET IDENTITY_INSERT [tbl_datetimeoffset] ON
MERGE INTO [tbl_datetimeoffset] AS Target
USING (VALUES
(1,2018-10-23 13:14:53.7638046 +0)
) AS Source ([id],[dto])
ON (Target.[id] = Source.[id])
WHEN MATCHED AND (
NULLIF(Source.[dto], Target.[dto]) IS NOT NULL OR NULLIF(Target.[dto], Source.[dto]) IS NOT NULL) THEN
UPDATE SET
[dto] = Source.[dto]
WHEN NOT MATCHED BY TARGET THEN
INSERT([id],[dto])
VALUES(Source.[id],Source.[dto])
WHEN NOT MATCHED BY SOURCE THEN
DELETE
;
GO
DECLARE @mergeError int
, @mergeCount int
SELECT @mergeError = @@ERROR, @mergeCount = @@ROWCOUNT
IF @mergeError != 0
BEGIN
PRINT 'ERROR OCCURRED IN MERGE FOR [tbl_datetimeoffset]. Rows affected: ' + CAST(@mergeCount AS VARCHAR(100)); -- SQL should always return zero rows affected
END
ELSE
BEGIN
PRINT '[tbl_datetimeoffset] rows affected by MERGE: ' + CAST(@mergeCount AS VARCHAR(100));
END
GO
SET IDENTITY_INSERT [tbl_datetimeoffset] OFF
GO
SET NOCOUNT OFF
GO
Should probably be handled here:
https://github.com/readyroll/generate-sql-merge/blob/81dacbe11a5f63086052bb2493ba80d23b67975a/master.dbo.sp_generate_merge.sql#L352
SET @db = 'USE [' + DB_NAME() +']';
This proc is awesome as I've used it in the past; however, seems to have issues with SQL Server v2019. (I am using an unaltered version of sp_generate_merge
). Any assistance is greatly appreciated.
I have 16 rows in the table (see Table Definition section below) and the output of the script does not have data from the table in the USING (VALUES...
section. The script does however perform an output in the grid that does represent the data.
--MERGE generated by 'sp_generate_merge' stored procedure, Version 0.9
--Originally by Vyas (http://vyaskn.tripod.com): sp_generate_inserts (build 22)
--Adapted for SQL Server 2008 by Daniel Nolan (http://danere.com)
SET NOCOUNT ON
SET IDENTITY_INSERT [Status] ON
MERGE INTO [Status] AS Target
USING (VALUES
) AS Source ([Id],[Name])
ON (Target.[Id] = Source.[Id])
WHEN MATCHED AND (Target.[Name] <> Source.[Name]) THEN
UPDATE SET
[Name] = Source.[Name]
WHEN NOT MATCHED BY TARGET THEN
INSERT([Id],[Name])
VALUES(Source.[Id],Source.[Name])
WHEN NOT MATCHED BY SOURCE THEN
DELETE;
GO
DECLARE @mergeError int
, @mergeCount int
SELECT @mergeError = @@ERROR, @mergeCount = @@ROWCOUNT
IF @mergeError != 0
BEGIN
PRINT 'ERROR OCCURRED IN MERGE FOR [Status]. Rows affected: ' + CAST(@mergeCount AS VARCHAR(100)); -- SQL should always return zero rows affected
END
ELSE
BEGIN
PRINT '[Status] rows affected by MERGE: ' + CAST(@mergeCount AS VARCHAR(100));
END
GO
SET IDENTITY_INSERT [Status] OFF
GO
SET NOCOUNT OFF
GO
CREATE TABLE [dbo].[Status](
[Id] [int] IDENTITY(1,1) NOT NULL,
[Name] [varchar](255) NOT NULL,
CONSTRAINT [PK_Status] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY],
CONSTRAINT [UK_Status_Name] UNIQUE NONCLUSTERED
(
[Name] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, OPTIMIZE_FOR_SEQUENTIAL_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
GO
The sample data below is the actual data and it appeared in the Results tab of SSMS, but I thought it should appear in the output script in the VALUES section.
(-1,'a')
,(0,'b')
,(1,'c')
,(2,'d')
,(3,'e')
,(4,'f')
,(5,'g')
,(6,'h')
,(7,'i')
,(8,'j')
,(9,'k')
,(10,'l')
,(11,'m')
,(12,'n')
,(13,'o')
,(14,'p')
I thought that @include_values
would be needed, but its default is 1. I was surprised to see that when I tried to use:
sp_generate_merge @table_name = 'Status', @include_values = 1
that the following error occurred: @include_values is not a parameter for procedure sp_generate_merge.
Microsoft SQL Server 2019 (RTM-CU15) (KB5008996) - 15.0.4198.2 (X64) Jan 12 2022 22:30:08 Copyright (C) 2019 Microsoft Corporation Developer Edition (64-bit) on Windows 10 Pro for Workstations 10.0 (Build 19044: ) (Hypervisor)
When calling the sproc using @include_rowsaffected = 0
results in an error:
Msg 10713, Level 15, State 1, Line 1
A MERGE statement must be terminated by a semi-colon (;).
For example:
EXEC dbo.sp_generate_merge @include_use_db = 0, @results_to_text = 1, @batch_separator = null, @nologo = 1, @include_rowsaffected = 0, @table_name = 'Printer'
Suggest removing the ';' from line 835 and adding to line 837. i.e.
IF @delete_if_not_matched=1
BEGIN
SET @output += @b + 'WHEN NOT MATCHED BY SOURCE THEN '
SET @output += @b + ' DELETE'
END
IF @include_rowsaffected = 1
BEGIN
SET @output += @b + 'OUTPUT $action INTO @mergeOutput'
END
SET @output += @b + ';'
As of today I've been getting the following error:
Msg 1934, Level 16, State 1, Procedure sp_generate_merge, Line 764 [Batch Start Line 1] SELECT failed because the following SET options have incorrect settings: 'QUOTED_IDENTIFIER'. Verify that SET options are correct for use with indexed views and/or indexes on computed columns and/or filtered indexes and/or query notifications and/or XML data type methods and/or spatial index operations.
The error itself seems to arise from line 778 in the file here, and MIGHT be an issue with FOR XML PATH
or AS XML
(somebody on the internet mentioned XML as the culprit).
BEGIN
SET @output += 'VALUES' + CAST((SELECT @b + val FROM @tab ORDER BY ID FOR XML PATH('')) AS XML).value('.', 'NVARCHAR(MAX)');
END
The initial problem was on SQL 2016. Order of troubleshooting:
SET QUOTED_IDENTIFIER ON
, against the DB and as an ALTER DATABASE
statementsp_generate_merge
against another database, which failedsp_generate_merge
exec sp_generate_merge @table_name = 'Employees', @results_to_text = 1
sp_generate_merge
into the database itself, with no luck.At this point, I'm thinking that something in the OS must have changed, or something's left behind with the uninstall / install of 2016 / 2019. If it's a change or a patch, I don't have any idea when it happened, because I usually only run sp_generate_merge
on a production server, to bring data back for test / dev purposes.
I would love it if someone told me this was my fault ... and how to fix it?
OS: Windows 10 Pro, Version 10.0.19044 Build 19044
MSSQL Versions: 2016 and 2019
SSMS Version: 15.0.18410.0
Possibly affecting this is that a junior engineer's test database is one which was scripted from their development machine and then run on their sandbox. I've listed the ALTER DATABASE
statements from that script below. I've ZERO idea how this could have affected the entire server, but for completeness:
ALTER DATABASE [MyDBTest] SET ANSI_NULL_DEFAULT OFF
ALTER DATABASE [MyDBTest] SET ANSI_NULLS OFF
ALTER DATABASE [MyDBTest] SET ANSI_PADDING OFF
ALTER DATABASE [MyDBTest] SET ANSI_WARNINGS OFF
ALTER DATABASE [MyDBTest] SET ARITHABORT OFF
ALTER DATABASE [MyDBTest] SET AUTO_CLOSE OFF
ALTER DATABASE [MyDBTest] SET AUTO_SHRINK OFF
ALTER DATABASE [MyDBTest] SET AUTO_UPDATE_STATISTICS ON
ALTER DATABASE [MyDBTest] SET CURSOR_CLOSE_ON_COMMIT OFF
ALTER DATABASE [MyDBTest] SET CURSOR_DEFAULT GLOBAL
ALTER DATABASE [MyDBTest] SET CONCAT_NULL_YIELDS_NULL OFF
ALTER DATABASE [MyDBTest] SET NUMERIC_ROUNDABORT OFF
ALTER DATABASE [MyDBTest] SET QUOTED_IDENTIFIER OFF
ALTER DATABASE [MyDBTest] SET RECURSIVE_TRIGGERS OFF
ALTER DATABASE [MyDBTest] SET DISABLE_BROKER
ALTER DATABASE [MyDBTest] SET AUTO_UPDATE_STATISTICS_ASYNC OFF
ALTER DATABASE [MyDBTest] SET DATE_CORRELATION_OPTIMIZATION OFF
ALTER DATABASE [MyDBTest] SET TRUSTWORTHY OFF
ALTER DATABASE [MyDBTest] SET ALLOW_SNAPSHOT_ISOLATION OFF
ALTER DATABASE [MyDBTest] SET PARAMETERIZATION SIMPLE
ALTER DATABASE [MyDBTest] SET READ_COMMITTED_SNAPSHOT OFF
ALTER DATABASE [MyDBTest] SET HONOR_BROKER_PRIORITY OFF
ALTER DATABASE [MyDBTest] SET RECOVERY FULL
ALTER DATABASE [MyDBTest] SET MULTI_USER
ALTER DATABASE [MyDBTest] SET PAGE_VERIFY CHECKSUM
ALTER DATABASE [MyDBTest] SET DB_CHAINING OFF
ALTER DATABASE [MyDBTest] SET FILESTREAM( NON_TRANSACTED_ACCESS = OFF )
ALTER DATABASE [MyDBTest] SET TARGET_RECOVERY_TIME = 60 SECONDS
ALTER DATABASE [MyDBTest] SET DELAYED_DURABILITY = DISABLED
ALTER DATABASE [MyDBTest] SET ACCELERATED_DATABASE_RECOVERY = OFF
EXEC sys.sp_db_vardecimal_storage_format N'MyDBTest', N'ON'
ALTER DATABASE [MyDBTest] SET QUERY_STORE = OFF
I just stumbled upon the examples in the script... what about adding a reference to those examples in the readme.md so that it is more discoverable?
Happy to submit a PR 😊
Hi all,
I have an issue with this in Azure Managed Instance related to one of the final steps.
EXEC sp_MS_marksystemobject sp_generate_merge
The following is the output.
Using Master database
Checking for the existence of this procedure
Created the procedure
Msg 2812, Level 16, State 62, Line 918
Could not find stored procedure 'sp_MS_marksystemobject'.
Granting EXECUTE permission on sp_generate_merge to all users
Done
Oddly I can see the 'missing' SP when looking manually through the list of system procs in the master database, but can not seem to find out why it does not resolve.
Has anyone seen/resolved this?
Or know of an alternative to get it working across all databases (without manual deploy to each database).
Not sure if this is concerned an issue, or known by design. When requesting to disable the FK constraints (@disable_constraints param set to 1) it changes the with check option to with nocheck (so it will no longer validate the existing data integrity).
To be clear generate-sql-merge instructs:
ALTER TABLE dbo.Table1 NOCHECK CONSTRAINT ALL
ALTER TABLE dbo.Table1 CHECK CONSTRAINT ALL
For example:
-- original
ALTER TABLE dbo.Table1 WITH CHECK ADD CONSTRAINT [FK_Table1_Table2] FOREIGN KEY([tbl2Id])
REFERENCES dbo.Table2 ([Id])
GO
ALTER TABLE dbo.Table1 CHECK CONSTRAINT [FK_Table1_Table2]
GO
-- after executing ALTER TABLE dbo.Table1 NOCHECK CONSTRAINT ALL
ALTER TABLE dbo.Table1 WITH NOCHECK ADD CONSTRAINT [FK_Table1_Table2] FOREIGN KEY([tbl2Id])
REFERENCES dbo.Table2 ([Id])
GO
ALTER TABLE dbo.Table1 NOCHECK CONSTRAINT [FK_Table1_Table2]
GO
-- after executing ALTER TABLE dbo.Table1 CHECK CONSTRAINT ALL
ALTER TABLE dbo.Table1 WITH NOCHECK ADD CONSTRAINT [FK_Table1_Table2] FOREIGN KEY([tbl2Id])
REFERENCES dbo.Table2 ([Id])
GO
ALTER TABLE dbo.Table1 CHECK CONSTRAINT [FK_Table1_Table2]
GO
-- after executing ALTER TABLE dbo.Table1 WITH CHECK CHECK CONSTRAINT ALL
ALTER TABLE dbo.Table1 WITH CHECK ADD CONSTRAINT [FK_Table1_Table2] FOREIGN KEY([tbl2Id])
REFERENCES dbo.Table2 ([Id])
GO
ALTER TABLE dbo.Table1 CHECK CONSTRAINT [FK_Table1_Table2]
GO
Columns of type geography cannot be compared with = or NULLIF. The merge statement generated for tables that have geography columns yields the following error:
"Invalid operator for data type. Operator equals equal to, type equals geography."
Comparing geography values needs to use the STEquals() function.
Hi,
instead of the NULLIF which can quiet unreadable I prefer using the EXCEPT version of change detection. It would be great if this tool could do that as well. They are described at http://www.made2mentor.com/2013/05/writing-t-sql-merge-statements-the-right-way/ .
MERGE #Customer_New AS Target
USING #Customer_Orig AS Source
ON Target.CustomerNum = Source.CustomerNum
WHEN MATCHED AND EXISTS
(SELECT Source.CustomerName, Source.Planet
EXCEPT
SELECT Target.CustomerName, Target.Planet)
THEN
UPDATE SET
Target.CustomerName = Source.CustomerName
,Target.Planet = Source.Planet
WHEN NOT MATCHED BY TARGET
THEN
INSERT (CustomerNum, CustomerName, Planet)
VALUES (CustomerNum, Source.CustomerName, Source.Planet)
WHEN NOT MATCHED BY SOURCE THEN DELETE;
This is just nit-picking, no real issue for me.
During a build in VS 2022, I get the following warning:
Warning SQL71558: The object reference [sys].[identity_columns].[OBJECT_ID] differs only by case from the object definition [master].[sys].[identity_columns].[object_id].
The warning occurs in the following line:
https://github.com/readyroll/generate-sql-merge/blob/9e9ddd2472317d54d9a45ec81aaff5b3aa0131dc/master.dbo.sp_generate_merge.sql#L538
The "OBJECT_ID" should be changed to "object_id" to match the case of the column.
The current warning can just be ignored for me. Maybe it could be an issue in an instance that was installed as case sensitive.
The code generates a trailing comma in the Update clause.
Aaron Bertrand explains why the use of INFORMATION_SCHEMA is not a good idea here
His comment below the post explains how to obtain ordinal position from sys.columns. The is_identity value is also available in sys.columns
Hi,
I was using this excellent sp and stumbled on an error. I tried to use it on a table with a varbinary column (by accident, I hadn't noticed the 12th column was storing images) and received the "Incorrect syntax near ')'" error. looking in the sp I found the WHEN statement for ('binary','varbinary') :
WHEN @Data_Type IN ('binary','varbinary')
THEN
'COALESCE(RTRIM(CONVERT(varchar(max),' + @Column_Name + ', 1))),''NULL'')'
The last line has one too many close brackets and should be:
'COALESCE(RTRIM(CONVERT(varchar(max),' + @Column_Name + ', 1)),''NULL'')'
Hi,
it would be very cool, if you would support isActive column.
This would mean that rows are never actually deleted but only marked by setting a column such as isActice to 0.
regards
Felix
Had this table:
CREATE TABLE [zip].[ZipTzDstExtension](
[TZ] [nvarchar](6) NOT NULL,
[DST] [nvarchar](1) NOT NULL,
[SQL] [nvarchar](100) NULL
CONSTRAINT [PK_work] PRIMARY KEY CLUSTERED
(
[TZ] ASC,
[DST] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO
It generated the data below - all was OK, except the ORDER and this time not, but previously I had a break in the syntax as the 1st row was somehow moved into the middle (there was a comma missing and there was an extra one at the start)...
<?x ---
USE [Test]
GO
--MERGE generated by 'sp_generate_merge' stored procedure
--Originally by Vyas (http://vyaskn.tripod.com/code): sp_generate_inserts (build 22)
--Adapted for SQL Server 2008+ by Daniel Nolan (https://twitter.com/dnlnln)
SET NOCOUNT ON
DECLARE @mergeOutput TABLE ( [DMLAction] VARCHAR(6) );
MERGE INTO [zip].[ZipTzDstExtension] AS [Target]
USING (VALUES
(N'EST+1',N'N',N'Atlantic Standard Time')
,(N'MST',N'N',N'US Mountain Standard Time')
,(N'PST-2',N'N',N'Hawaiian Standard Time')
,(N'PST-3',N'N',N'Samoa Standard Time')
,(N'PST-4',N'N',N'UTC+12')
,(N'PST-5',N'N',N'Central Pacific Standard Time')
,(N'PST-6',N'N',N'Vladivostok Standard Time')
,(N'PST-7',N'N',NULL)
,(N'CST',N'Y',N'Central Standard Time')
,(N'EST',N'Y',N'Eastern Standard Time')
,(N'EST+1',N'Y',NULL)
,(N'GMT+1',N'Y',NULL)
,(N'MST',N'Y',N'Mountain Standard Time')
,(N'PST',N'Y',N'Pacific Standard Time')
,(N'PST-1',N'Y',N'Alaskan Standard Time')
,(N'PST-2',N'Y',N'Aleutian Standard Time')
) AS [Source] ([TZ],[DST],[SQL])
ON ([Target].[DST] = [Source].[DST] AND [Target].[TZ] = [Source].[TZ])
WHEN MATCHED AND (
NULLIF([Source].[SQL], [Target].[SQL]) IS NOT NULL OR NULLIF([Target].[SQL], [Source].[SQL]) IS NOT NULL) THEN
UPDATE SET
[Target].[SQL] = [Source].[SQL]
WHEN NOT MATCHED BY TARGET THEN
INSERT([TZ],[DST],[SQL])
VALUES([Source].[TZ],[Source].[DST],[Source].[SQL])
WHEN NOT MATCHED BY SOURCE THEN
DELETE
OUTPUT $action INTO @mergeOutput;
DECLARE @mergeError int
, @mergeCount int, @mergeCountIns int, @mergeCountUpd int, @mergeCountDel int
SELECT @mergeError = @@ERROR
SELECT @mergeCount = COUNT(1), @mergeCountIns = SUM(IIF([DMLAction] = 'INSERT', 1, 0)), @mergeCountUpd = SUM(IIF([DMLAction] = 'UPDATE', 1, 0)), @mergeCountDel = SUM (IIF([DMLAction] = 'DELETE', 1, 0)) FROM @mergeOutput
IF @mergeError != 0
BEGIN
PRINT 'ERROR OCCURRED IN MERGE FOR [zip].[ZipTzDstExtension]. Rows affected: ' + CAST(@mergeCount AS VARCHAR(100)); -- SQL should always return zero rows affected
END
ELSE
BEGIN
PRINT '[zip].[ZipTzDstExtension] rows affected by MERGE: ' + CAST(COALESCE(@mergeCount,0) AS VARCHAR(100)) + ' (Inserted: ' + CAST(COALESCE(@mergeCountIns,0) AS VARCHAR(100)) + '; Updated: ' + CAST(COALESCE(@mergeCountUpd,0) AS VARCHAR(100)) + '; Deleted: ' + CAST(COALESCE(@mergeCountDel,0) AS VARCHAR(100)) + ')' ;
END
GO
SET NOCOUNT OFF
GO
?>
I appreciate the work on this, but is it possible to have a way to make the source be a "SELECT columns FROM sourceTable" type query instead of using static values? I'd like to be able to dev this on a test system and push to any other system where those values could potentially be different depending on what customers have put in the tables.
When @cols_to_join_on
is specified, an error is set specifying that a valid @cols_to_join_on
has not been set. When explicitly specifying the schema, it works.
It appears that at line 438 the filter is matching c.TABLE_SCHEMA
to @schema
, requiring the @schema
parameter be defined, while on line 470 the filter is matching c.TABLE_SCHEMA
to COALLESCE(@schema, SCHEMA_NAME())
, allowing the current schema to be used if not specified.
A declarative, efficient, and flexible JavaScript library for building user interfaces.
🖖 Vue.js is a progressive, incrementally-adoptable JavaScript framework for building UI on the web.
TypeScript is a superset of JavaScript that compiles to clean JavaScript output.
An Open Source Machine Learning Framework for Everyone
The Web framework for perfectionists with deadlines.
A PHP framework for web artisans
Bring data to life with SVG, Canvas and HTML. 📊📈🎉
JavaScript (JS) is a lightweight interpreted programming language with first-class functions.
Some thing interesting about web. New door for the world.
A server is a program made to process requests and deliver data to clients.
Machine learning is a way of modeling and interpreting data that allows a piece of software to respond intelligently.
Some thing interesting about visualization, use data art
Some thing interesting about game, make everyone happy.
We are working to build community through open source technology. NB: members must have two-factor auth.
Open source projects and samples from Microsoft.
Google ❤️ Open Source for everyone.
Alibaba Open Source for everyone
Data-Driven Documents codes.
China tencent open source team.