Creating “Not Exists”/Insert Into Statements - Stu

问题:

I need a script that generate insert statements but with check for if the data doesn't already exist, this because it should be periodically run on parallell systems where different dtata will be added to the systems but we want them tables to be in sync. I have the basic ides and borrowed parts of code but get a syntax error i have trouble solving.

I'm basing my code on the code Param Yadav showed at Converting Select results into Insert script - SQL Server but I need to check for data already in the table. (I need to add more "bells & whistles later, but take this step-by-step)

My own main addition is the @NOT_EXISTS part which should be in the WHERE clause of the NOT EXISTS check. If I replace that with a plain WHERE 0=1 I get no syntax error so it indicates the error is in my @NOT_EXISTS string.

Edit: Yesterday I thought I had an answer to my own question but when running on "real data" I saw that some lines are too long for QUOTENAME, I have to fix those quotation marks "manually" (concats in script) instead...

SET NOCOUNT ON

DECLARE @CSV_COLUMN VARCHAR(MAX),
    @QUOTED_DATA VARCHAR(MAX),
    @NOT_EXISTS VARCHAR(MAX),
    @SQL_KOD VARCHAR(MAX),
    @TABLE_NAME VARCHAR(MAX),
    @FILTER_CONDITION VARCHAR(MAX)='',
    @FIRST_COL INT,
    @LAST_COL INT

/* INPUT DATA */
SELECT @TABLE_NAME = 'WorkflowError'
SELECT @FIRST_COL = 2
SELECT @LAST_COL = 4
/* */

SELECT @CSV_COLUMN=STUFF
(
    (
     SELECT ',['+ NAME +']' FROM sys.all_columns 
     WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND 
     is_identity!=1 FOR XML PATH('')
    ),1,1,''
)

--SELECT @CSV_COLUMN

SELECT @QUOTED_DATA=STUFF
(
    (
     SELECT ' ISNULL(QUOTENAME('+NAME+','+QUOTENAME('''','''''')+'),'+'''NULL'''+')+'','''+'+' FROM sys.all_columns 
     WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND 
     is_identity!=1 FOR XML PATH('')
    ),1,1,''
)

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

SELECT @QUOTED_DATA

SELECT @NOT_EXISTS=STUFF
(
    (
     SELECT ' ['+ COLUMN_NAME +']=', 'ISNULL(QUOTENAME('+COLUMN_NAME+','+QUOTENAME('''','''''')+'),'+'''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 @NOT_EXISTS=SUBSTRING(@NOT_EXISTS,1,LEN(@NOT_EXISTS)-4)

SELECT @NOT_EXISTS
--SELECT @NOT_EXISTS=' 0=1 '

SELECT @SQL_KOD='SELECT ''
    IF NOT EXISTS(SELECT 1 
    FROM ' + @TABLE_NAME + ' WHERE ' + @NOT_EXISTS + ')
    BEGIN
        INSERT INTO '+@TABLE_NAME+'('+@CSV_COLUMN+')
        VALUES('''+'+'+@QUOTED_DATA+'+'+''')
    END
    GO '''+' Insert_Scripts 
FROM '+@TABLE_NAME + @FILTER_CONDITION

SELECT @SQL_KOD
EXECUTE (@SQL_KOD)

GO

[stackoverflow won't let me post code unless it's formatted, but then the strings below won't be as they are created in the script...]
When I do SELECT @NOT_EXISTS=' 0=1 ' I get an INSERT line for each row in my table:

IF NOT EXISTS(SELECT 1 FROM WorkflowError WHERE  0=1 )
    BEGIN
        INSERT INTO WorkflowError([TargetSystem],[ErrorCode],[ErrorText],[RetryMaxCount],[RetryStrategyName],[ErrorDescription])
        VALUES('EttLiv','800','Value cannot be null.  Parameter name: source','0',NULL,'Value cannot be null.  Parameter name: source')
    END
GO 

With my @NOT_EXISTS code the @SQL_KOD string becomes this:

SELECT 'IF NOT EXISTS(SELECT 1 FROM WorkflowError
                      WHERE [TargetSystem]=ISNULL(QUOTENAME(TargetSystem,''''),'NULL'))
BEGIN
    INSERT INTO WorkflowError([TargetSystem],[ErrorCode],[ErrorText],[RetryMaxCount],[RetryStrategyName],[ErrorDescription])
    VALUES('+ISNULL(QUOTENAME(TargetSystem,''''),'NULL')+','
    + ISNULL(QUOTENAME(ErrorCode,''''),'NULL')+','
    + ISNULL(QUOTENAME(ErrorText,''''),'NULL')+','
    + ISNULL(QUOTENAME(RetryMaxCount,''''),'NULL')+','
    + ISNULL(QUOTENAME(RetryStrategyName,''''),'NULL')+','
    + ISNULL(QUOTENAME(ErrorDescription,''''),'NULL')+')
END
GO ' Insert_Scripts FROM WorkflowError
However, trying to execute that @SQL_KOD line just gives:

Msg 156, Level 15, State 1, Line 3
Incorrect syntax near the keyword 'NULL'.

...and I can't find out where I have done wrong, if it's in my thinking or if it's just a misplaced quotation mark...

回答1:

Where do you expect @SQL_KOD to get its values from? Because if you are retrieving your values for TargetSystem / ErrorCode / ... / ErrorDescription from somewhere outside of your insert statement, I would expect a "from" statement. If you want to input variables, you are missing both the definition of the variables and the @-sign in front of the variable name.

As far as keeping quotes happy: try writing your code with QUOTED_IDENTIFIER OFF - you can create the entire @SQL_KOD variable by writing between double quotes ("), and single quotes would behave like normal quotation marks.

A very basic re-write of your code could be something as follows:

SET QUOTED_IDENTIFIER OFF 

DECLARE @SQL_KOD VARCHAR(MAX)

SET @SQL_KOD = 

"DECLARE @WorkFlowError TABLE ([TargetSystem] NVARCHAR(200),[ErrorCode] NVARCHAR(200))

IF NOT EXISTS ( SELECT 1 FROM @WorkFlowError )
BEGIN
    INSERT INTO @WorkFlowError ([TargetSystem],[ErrorCode])
    SELECT ISNULL(QUOTENAME([TargetSystem],''''),'NULL')
        , ISNULL(QUOTENAME([ErrorCode],''''),'NULL')
    FROM (
        SELECT [TargetSystem]='Foo'
            , [ErrorCode]='Bar'
    ) src
END";

回答2:

I originally used QUOTENAME as in the Param Yadav script I borrowed from but that function can't handle long strings. It doesn't complain, just returns NULL if the string is too long. Now the script is less readable (long lines of quotation marks) but now works.

SET NOCOUNT ON

DECLARE @CSV_COLUMN VARCHAR(MAX),
        @QUOTED_DATA VARCHAR(MAX),
        @NOT_EXISTS VARCHAR(MAX),
        @SQL_KOD VARCHAR(MAX),
        @TABLE_NAME VARCHAR(MAX),
        @FILTER_CONDITION VARCHAR(MAX),
        @FIRST_COL INT,
        @LAST_COL INT


/* INPUT DATA */
SELECT @TABLE_NAME = 'WorkflowError'
SELECT @FIRST_COL = 2
SELECT @LAST_COL = 4
SELECT @FILTER_CONDITION = ''
/* */

SELECT @CSV_COLUMN=STUFF
(
    (
     SELECT ',['+ NAME +']' FROM sys.all_columns 
     WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND 
     is_identity!=1 FOR XML PATH('')
    ),1,1,''
)

SELECT @QUOTED_DATA=STUFF
(
    (
     SELECT ' ISNULL('''''''' + REPLACE('+NAME+','''''''','''''''''''') + '''''''','''+'NULL'''+''+')+'',''+'
     FROM sys.all_columns 
     WHERE OBJECT_ID=OBJECT_ID(@TABLE_NAME) AND 
     is_identity!=1 FOR XML PATH('')
    ),1,1,''
)

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

SELECT @NOT_EXISTS=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 @NOT_EXISTS=SUBSTRING(@NOT_EXISTS,1,LEN(@NOT_EXISTS)-6)

SELECT @SQL_KOD='SELECT ''IF NOT EXISTS(SELECT 1 FROM ' + @TABLE_NAME + ' WHERE ' + @NOT_EXISTS + ' + ' + ''') BEGIN INSERT INTO '+@TABLE_NAME+'('+@CSV_COLUMN+')VALUES('''+'+'+@QUOTED_DATA+'+'+''') END '''+' Insert_Scripts FROM ' + @TABLE_NAME + ' ' + @FILTER_CONDITION

EXECUTE (@SQL_KOD)

SET NOCOUNT OFF