Create an UPDATE statement for every row in a table

I want to update a table on one server with data from a table on another server. The tables have the same columns and I just want to sync the data. I have earlier made a script that creates insert statements for these tables (put out a question to SO and answered it myself…) but the seemingly simpler task of creating UPDATE statements now eludes me. Maybe because I’m just back from holiday.

For reference, here’s my “INSERT question”: Creating “Not Exists”/Insert Into Statements – Stumped by nothing (NULL)

I’m trying to use my INSERT statements code but I’m either not getting multiple rows or just getting hard-to-debug error messages (Error close to NULL)

So, what I want to do is creating statements like this, one for each row in the table:

UPDATE WorkflowError 
   SET [ErrorDescription] = 'Error creating email' 
 WHERE [TargetSystem]='Fullmaktsnoden' 
   AND  [ErrorCode]='999' 
   AND  [ErrorText]='Error creating email' 
   AND [ErrorDescription] IS NULL

I could use some general pointers like "Since you're now creating UPDATE statements instead of INSERT you need to think about..."

This is a table used for integration of several systems. We “collect” messages we get from other systems and record them in ErrorDescription. If it’s too long or misleading we put a shorter explanation in Errortext to show for the user. (in this example I happened to use the same text, “error creating email”, but in the real system the error description we get is much longer)

One try is a code like this:

USE FullmaktsnodenProcess
GO

SET NOCOUNT ON

DECLARE @QUOTED_DATA VARCHAR(MAX),
        @DOES_EXIST VARCHAR(MAX),
        @SQL_KOD VARCHAR(MAX),
        @TABLE_NAME VARCHAR(MAX),
        @FIRST_COL INT,
        @LAST_COL INT,
        @THE_COLUMN VARCHAR(MAX)


/* INPUT DATA */
SELECT @TABLE_NAME = 'WorkflowError'
SELECT @FIRST_COL = 2 -- First column for Exists check
SELECT @LAST_COL = 4 -- Last column for Exists check
SELECT @THE_COLUMN = 'ErrorDescription'
/* */

SELECT @QUOTED_DATA=STUFF
(
    (
     SELECT ' ISNULL('''''''' + REPLACE('+COLUMN_NAME+','''''''','''''''''''') + '''''''','''+'NULL'''+''+')+'',''+'
     FROM information_schema.columns 
     WHERE table_name = @TABLE_NAME AND 
     COLUMN_NAME = @THE_COLUMN
     FOR XML PATH('')
    ),1,1,''
)

SELECT @QUOTED_DATA=SUBSTRING(@QUOTED_DATA,1,LEN(@QUOTED_DATA)-5)

SELECT @QUOTED_DATA

SELECT @DOES_EXIST=STUFF
(
    (
     SELECT ' ['+ COLUMN_NAME +']='' + ', 'ISNULL('''''''' + REPLACE('+COLUMN_NAME+','''''''','''''''''''') + '''''''','''+'NULL'''+''+')+'' AND '
     FROM information_schema.columns 
     WHERE table_name = @TABLE_NAME AND 
     ordinal_position BETWEEN @FIRST_COL AND @LAST_COL
     FOR XML PATH('')
    ),1,1,''
)

SELECT @DOES_EXIST


SELECT @SQL_KOD='SELECT ''UPDATE ' + @TABLE_NAME + ' SET ' +  QUOTENAME(@THE_COLUMN) + ' = ' + @QUOTED_DATA + ' WHERE ' + @DOES_EXIST + @THE_COLUMN + ' IS NULL'''

SELECT @SQL_KOD

EXECUTE (@SQL_KOD)

SET NOCOUNT OFF

GO

---

Trying to execute @SQL_KOD gives: “Msg 156, Level 15, State 1, Line 3 Incorrect syntax near the keyword ‘NULL’.”

From test run, with EXECUTE (@SQL_KOD) commented out:

@QUOTED_DATA = ISNULL('''' + REPLACE(ErrorDescription,'''','''''') + '''','NULL')

@DOES_EXIST = [TargetSystem]=' + ISNULL('''' + REPLACE(TargetSystem,'''','''''') + '''','NULL')+' AND  [ErrorCode]=' + ISNULL('''' + REPLACE(ErrorCode,'''','''''') + '''','NULL')+' AND  [ErrorText]=' + ISNULL('''' + REPLACE(ErrorText,'''','''''') + '''','NULL')+' AND 

@SQL_KOD = SELECT 'UPDATE WorkflowError SET [ErrorDescription] = ISNULL('''' + REPLACE(ErrorDescription,'''','''''') + '''','NULL') WHERE [TargetSystem]=' + ISNULL('''' + REPLACE(TargetSystem,'''','''''') + '''','NULL')+' AND  [ErrorCode]=' + ISNULL('''' + REPLACE(ErrorCode,'''','''''') + '''','NULL')+' AND  [ErrorText]=' + ISNULL('''' + REPLACE(ErrorText,'''','''''') + '''','NULL')+' AND ErrorDescription IS NULL'

Answer

This seems to fit the bill. Qutation marks in data are now correctly doubled and the end result is one UPDATE statement for each row in the table, as I wanted. As I mentioned, reading from information_schema.columns is unnecessary when I’m here just working with one known column, but maybe when I’m modyfying this script for another table it’s not so…

USE FullmaktsnodenProcess
GO

SET NOCOUNT ON

DECLARE @QUOTED_DATA VARCHAR(MAX),
        @DOES_EXIST VARCHAR(MAX),
        @SQL_KOD VARCHAR(MAX),
        @TABLE_NAME VARCHAR(MAX),
        @FIRST_COL INT,
        @LAST_COL INT,
        @THE_COLUMN VARCHAR(MAX)


/* INPUT DATA */
SELECT @TABLE_NAME = 'WorkflowError'
SELECT @FIRST_COL = 2 -- First column for Exists check
SELECT @LAST_COL = 4 -- Last column for Exists check
SELECT @THE_COLUMN = 'ErrorDescription'
/* */

SELECT @QUOTED_DATA=STUFF
(
    (
     SELECT ' ISNULL(REPLACE('+QUOTENAME(COLUMN_NAME)+','''''''','''''''''''') + '''''''','''+'NULL'''+''+')+'',''+'
     FROM information_schema.columns 
     WHERE table_name = @TABLE_NAME AND 
     COLUMN_NAME = @THE_COLUMN
     FOR XML PATH('')
    ),1,1,''
)

SELECT @QUOTED_DATA=SUBSTRING(@QUOTED_DATA,1,LEN(@QUOTED_DATA)-5)

--SELECT @QUOTED_DATA

SELECT @DOES_EXIST=STUFF
(
    (
     SELECT ' '+ QUOTENAME(COLUMN_NAME) +'='''''' + ', 'ISNULL(REPLACE('+QUOTENAME(COLUMN_NAME)+','''''''','''''''''''') + '''''''','''+'NULL'''+''+')+'' AND '
     FROM information_schema.columns 
     WHERE table_name = @TABLE_NAME AND 
     ordinal_position BETWEEN @FIRST_COL AND @LAST_COL
     FOR XML PATH('')
    ),1,1,''
)

-- SELECT @DOES_EXIST

SELECT @SQL_KOD='SELECT ''UPDATE ' + @TABLE_NAME + ' SET ' +  QUOTENAME(@THE_COLUMN) + ' = ' + ''''''' + ' + @QUOTED_DATA + ' + ' + ''' WHERE ' + @DOES_EXIST + @THE_COLUMN + ' IS NULL ' + ''' Update_Script ' + 'FROM ' + @TABLE_NAME

-- SELECT @SQL_KOD

EXECUTE (@SQL_KOD)

SET NOCOUNT OFF

GO