danere (4/3/2012)
Thanks for that John, I've revised the script to include the Primary Key check.Unfortunately I'm not able to edit the original article, so have zipped the SQL file here:
You might also consider:
DECLARE @db varchar(120);
set @db = 'USE ' + DB_NAME();
print @db;
print '';
PRINT '--MERGE generated by ''sp_generate_merge'' stored procedure, Version 0.9'
....
@Actual_Values nvarchar(max)
....
SET @Actual_Values =
'SELECT @valuesOUT = IsNull(@valuesOUT, '''') + ' +
CASE WHEN @top IS NULL OR @top < 0 THEN '' ELSE ' TOP ' + LTRIM(STR(@top)) + ' ' END +
'''' +
' '' + char(10) + CASE WHEN ROW_NUMBER() OVER (ORDER BY ' + @PK_column_list + ') = 1 THEN '' '' ELSE '','' END + ''(''+ ' + @Actual_Values + '+'')''' + ' ' +
COALESCE(@from,' FROM ' + @Source_Table_Qualified + ' (NOLOCK)')
....
DECLARE @values nvarchar(max);
DECLARE @ParmDefinition nvarchar(500);
SET @ParmDefinition = N'@valuesOUT nvarchar(max) OUTPUT';
EXECUTE sp_executesql @actual_values, @ParmDefinition, @valuesOUT=@values OUTPUT;
print @values;
....
This skips outputing the results to a table, sets the DB, and goes directly to the messages window, ready to copy and run.