创建"Not Exists" /插入语句 - 一无所获 (NULL)

Question

我需要一个生成插入语句的脚本，但要检查数据是否不存在，因为它应该在parallell系统上定期运行，其中将添加不同的DTATA系统，但我们希望它们与表保持同步。我有基本的IDE和借用的代码部分，但要解决我解决的语法错误。

我将代码基于代码param yadav显示，以将选择结果转换为插入脚本-SQL Server，但我需要检查表中的数据。(以后我需要添加更多"铃铛"，但请逐步添加(

我自己的主要添加是@NOT_EXISTS部分，该部分应位于"不存在"检查中的Where子句中。如果我用一个平原替换为0 = 1我会收到语法错误，因此它表明错误在我的@Not_Exists字符串中。

编辑：昨天我以为我对自己的问题有一个答案相反...

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...

Answer 1

您期望@sql_kod从哪里获取其值？因为如果您要从插入语句之外的某个地方检索目标系统/错误代码/errordescription的值，我希望从"语句中使用。如果要输入变量，则既缺少变量的定义，又错过了变量名称前面的 @-sign。

至于保持报价快乐：尝试使用QuoteD_Identifier打开代码 - 您可以通过在double Quotes之间写入整个@SQL_KOD变量("(，单句话的行为将像正常引号一样行为。

。

非常基本的代码重写可能是以下内容：

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";

Answer 2

我最初使用的定制名称，如我从我借来的param yadav脚本中所述，但该函数无法处理长字符串。它不会抱怨，如果字符串太长，只需返回零即可。现在，脚本不太可读(引号长行(，但现在有效。

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