Создание «не существует»/вставка в операторы — ничем не озадачен (NULL)
Мне нужен сценарий, который генерирует операторы вставки, но с проверкой, если данные еще не существуют, потому что его следует периодически запускать в параллельных системах, где в системы будут добавляться разные данные, но мы хотим, чтобы их таблицы были синхронизированы. У меня есть основные идеи и заимствованные части кода, но я получаю синтаксическую ошибку, которую не могу решить.
Я основываю свой код на коде, показанном Парамом Ядавом в Преобразование результатов Select в скрипт Insert — SQL Server, но мне нужно проверить данные, уже находящиеся в таблице. (Мне нужно добавить больше «наворотов и свистков» позже, но делайте это шаг за шагом)
Моим основным дополнением является часть @NOT_EXISTS, которая должна быть в предложении WHERE проверки NOT EXISTS. Если я заменю это простым WHERE 0 = 1, я не получу синтаксической ошибки, поэтому это указывает на то, что ошибка находится в моей строке @NOT_EXISTS.
Редактировать: вчера я думал, что у меня есть ответ на мой собственный вопрос, но при работе с «реальными данными» я увидел, что некоторые строки слишком длинны для QUOTENAME, вместо этого я должен исправить эти кавычки «вручную» (concats в скрипте) .. .
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...
Я работаю только с одной таблицей в этом сценарии T-SQL... Основная проблема для отладки заключается в том, что это сценарий, создающий новый сценарий (в строковой переменной @SQL_KOD), который затем выполняется - трудно сохранить эти кавычки счастливый.
Кстати, при тестовом запуске моего скрипта для LAST_COL было установлено значение 2, чтобы упростить предложение WHERE, у меня есть только один столбец, включенный в мой пример SQL_KOD - [TargetSystem]=ISNULL(QUOTENAME(TargetSystem,''''),'NULL' ) часть SQL_KOD.
Строка QUOTED_DATA — это то, что попадает в список данных VALUES, и там ISNULL (QUOTENAME (TargetSystem, ''''), 'NULL') разрешается в 'EttLiv', который является содержимым столбца TargetSystem. NOT_EXISTS входит в предложение WHERE, но здесь очень похожее [TargetSystem]=ISNULL(QUOTENAME(TargetSystem,''''),'NULL') выдает ошибку T-SQL. Похоже, что разрешение работает по-разному в предложении WHERE по сравнению с VALUES...
Если я для проверки заменю WHATEVER на NULL, сообщение об ошибке, которое я получаю, несколько изменится, поскольку NULL - это T-SQL, а WHATEVER - нет. Однако у меня все еще есть проблемы с пониманием того, что не так... ```t-sql SELECT @NOT_EXISTS=STUFF ( ( SELECT ' ['+ COLUMN_NAME +']=', 'ISNULL(QUOTENAME('+COLUMN_NAME+','+QUOTENAME ('''','''''')+'),'+'''НИЧЕГО'''+') И ' FROM information_schema.columns, ГДЕ table_name = @TABLE_NAME AND ordinal_position BETWEEN @FIRST_COL AND @LAST_COL FOR XML PATH('') ),1,1,'' )
Сообщение об ошибке: Msg 102, уровень 15, состояние 1, строка 3. Неверный синтаксис рядом с ')) BEGIN INSERT INTO WorkflowError([TargetSystem],[ErrorCode],[ErrorText],[RetryMaxCount],[RetryStrategyName],[ ErrorDescription])».
Надеюсь, stackoverflow.com/users/6165840/парам-ядав увидит это и научит меня... Я написал несколько более сложных сценариев T-SQL, чем этот, но в данный момент я чувствую себя необразованным и не понимаю, что делаю неправильно.
Я, конечно, скопировал полученный код (SQL_KOD) в SSMS и увидел, что он жалуется на NULL, а также увидел, что если я поставлю две одинарные кавычки вокруг NULL вместо одной, я не получу синтаксическую ошибку (но не желаемый результат либо ). Я экспериментирую с редактированием этого фрагмента кода, но может быть ближе, но все еще не сигара. Указывает, что мне нужна одиночная кавычка перед ISNULL в предложении WHERE для разрешения предложения WHERE, поскольку в противном случае это просто строка, но затем он жалуется на QUOTENAME.
Ответы 2
Откуда вы ожидаете, что @SQL_KOD получит свои значения? Потому что, если вы извлекаете свои значения для TargetSystem / ErrorCode / ... / ErrorDescription откуда-то за пределами вашего оператора вставки, я бы ожидал оператора «от». Если вы хотите ввести переменные, вам не хватает как определения переменных, так и знака @ перед именем переменной.
Что касается сохранения кавычек: попробуйте написать свой код с QUOTED_IDENTIFIER OFF - вы можете создать всю переменную @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";
Как видно в конце SQL_KOD: FROM '+@TABLE_NAME, которая является таблицей "workflowerror". Все значения для части оператора INSERT правильно берутся оттуда, возможно, это отличается от предложения WHERE. Я посмотрю в QUOTED_IDENTIFIER OFF - может быть, тогда будет легче увидеть, где я ошибаюсь.
... и мои переменные, конечно же, объявлены в начале. Первоначальным, на котором я строю, был сценарий, который создал хранимую процедуру с параметрами, я жду с sp, пока все не заработает, и вместо этого получу свою маленькую /* INPUT DATA */ часть.
В качестве конечного результата я бы хотел, чтобы НЕ СУЩЕСТВУЕТ/ВСТАВЛЯЕТСЯ В такие строки: ```sql IF NOT EXISTS(SELECT 1 FROM WorkflowError WHERE [TargetSystem]='EttLiv' ) BEGIN INSERT INTO WorkflowError([TargetSystem],[ErrorCode ],[ErrorText],[RetryMaxCount],[RetryStrategyName],[ErrorDescription]) VALUES('EttLiv','800','Значение не может быть нулевым. Имя параметра: source','0', NULL,'Значение не может быть нулевым. Имя параметра: источник') END GO
Первоначально я использовал QUOTENAME, как в сценарии Param Yadav, который я позаимствовал, но эта функция не может обрабатывать длинные строки. Он не жалуется, просто возвращает NULL, если строка слишком длинная. Теперь скрипт менее читаем (длинные строки кавычек), но работает.
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
Я попытался показать одну или две строки, в которых QUOTED_DATA терпит неудачу, но они слишком длинные, чтобы включать их в комментарий...
Добавлена одна строка, которую скрипт не обрабатывает ОК после скрипта выше, хотя StackOverflow переформатировал ее.
Я пытался создать несколько разных тестовых данных, но единственное, что общего у неудачных строк, это то, что они длинные, символы вроде \@([] не беспокоят сценарий. Но все переменные - это VARCHAR(MAX), а QUOTENAME - это только используется в именах столбцов, например: ISNULL(QUOTENAME(TargetSystem,''''),'NULL')+','+ ISNULL(QUOTENAME(ErrorCode,''''),'NULL')+','+ ISNULL (QUOTENAME(ErrorText,''''),'NULL')+','+ ISNULL(QUOTENAME(RetryMaxCount,''''),'NULL')+','+ ISNULL(QUOTENAME(RetryStrategyName,''' '),'NULL')+','+ ISNULL(QUOTENAME(ErrorDescription,''''),'NULL')
Кстати, самый длинный текст в столбце Errortext составляет 3890 символов.
Итак, QUOTENAME нельзя использовать, что я могу использовать вместо этого, чтобы заключать данные в кавычки? Должно быть возможно обойтись без использования какой-либо функции, я думаю, просто еще сложнее отслеживать все кавычки...
Чтобы заменить QUOTENAME и то, что она делает, я уверен, что мне нужно будет использовать REPLACE :-) Мне нужно получить что-то вроде SELECT ISNULL('''' + REPLACE(TargetSystem,'''',''''' ')+ '''','NULL')...
SELECT ISNULL('''' + REPLACE(TargetSystem,'''',''''''),'NULL')+','+ ISNULL('''' + REPLACE(ErrorCode,'''', ''''''),'NULL')+','+ ISNULL('''' + REPLACE(ErrorText,'''',''''''),'NULL')+','+ ISNULL('''' + REPLACE(RetryMaxCount,'''',''''''),'NULL')+','+ ISNULL('''' + REPLACE(RetryStrategyName,'''',' '''''),'NULL')+','+ ISNULL('''' + REPLACE(ErrorDescription,'''',''''''),'NULL') FROM WorkflowError работает. Теперь мне нужно создать код, который создает этот код...
У меня не было необходимости использовать @FILTER_CONDITION, поэтому он не тестировался с моим скриптом, но кроме этого теперь он работает нормально.
У вас есть структура обеих таблиц одинаковая?