Я пытаюсь обновить строки данных с помощью строки замены (созданной другим SP). У меня есть следующая таблица данных, в которой показаны значения expacct и замены glseg1 и glseg2 по умолчанию:
Я пытаюсь обновить номер expacct с помощью обновленного номера expacct, сгенерированного ReplacementString. ReplacementString находится в таблице и связан с expacct по умолчанию:
Каждый раздел expacct (число, установленное между дефисами) может быть обновлено. Вкратце, для каждого раздела expacct ReplacementString должна принимать значение expacct по умолчанию, использовать то, что есть в настоящее время, если перезапись не разрешена (например, подстрока ('@ Acct', 1,4)) или перезаписывать значением из назначенный столбец glseg, используя значение по умолчанию, если значение glseg не существует (например, rtrim (ltrim (isnull (nullif (d.glseg2, ''), substring ('@ Acct', 10,4)))))
У меня есть несколько работающая версия кода, но строка замены не обновляется для каждой обновляемой строки, похоже, что используемая ReplacementString является последней в приведенной выше таблице.
Что мне нужно увидеть в этой выходной таблице, так это expacct для ID 1, равного 6720-010-0000 (значение по умолчанию для первого набора, значение glseg1 для второго набора и 0000 для третьего набора, поскольку glseg2 пуст). Обновленное значение expacct для строки с ID 2 верное.
Мы должны поддерживать SQL до версии 2008 R2, поэтому, пожалуйста, ограничьте решения теми, которые работают с SQL 2008 R2.
Вот полный код SQL, который у меня есть:
if object_id(N'tempdb..#TimeData') is not null drop table #TimeData
create table #TimeData(ID int, expacct varchar(45), glseg1 varchar(10), glseg2 varchar(10))
insert into #TimeData
values(1, '6720-000-0000', '010', '')
, (2, '6720-999-0000', '030', '0404')
select * from #TimeData
-- create table of all possible account numbers from ALL_TimeCardDetail with their associated replacement strings
if object_id(N'tempdb..#AccountNumbers') is not null drop table #AccountNumbers
create table #AccountNumbers(Original varchar(100), ReplacementString nvarchar(max), Updated varchar(100))
insert into #AccountNumbers
values('6720-000-0000', 'substring(''@Acct'',1,4) + ''-'' + rtrim(ltrim(isnull(nullif (d.glseg1, ''''), substring(''@Acct'',6,3)))) + ''-'' + rtrim(ltrim(isnull(nullif (d.glseg2, ''''), substring(''@Acct'',10,4))))', '')
, ('6720-999-0000', 'substring(''@Acct'',1,4) + ''-'' + substring(''@Acct'',6,3) + ''-'' + rtrim(ltrim(isnull(nullif (d.glseg2, ''''), substring(''@Acct'',10,4))))', '')
select * from #AccountNumbers
declare @cmd nvarchar(max)
select @cmd = N'update #TimeData set expacct = ' + replace(n.ReplacementString, '@Acct', d.expacct) + '
from #TimeData d
inner join #AccountNumbers n
on d.expacct = n.Original'
from #TimeData d
inner join #AccountNumbers n
on d.expacct = n.Original
print @cmd
exec (@cmd)
select * from #TimeData
Ух. Это совершенно новый уровень возможностей SQL-инъекций. Пожалуйста, закрепите этот стол своей жизнью, заменив его нитками, и примените к нему одитинг для хорошей меры. На самом деле, настоятельно рекомендуется отказаться от этого вместо этого - сколько различных заменяющих строк вам нужно В самом деле, и не можете ли вы сократить это до нескольких случаев, которые вы кодируете простым числом и включаете с помощью CASE
? Или как насчет использования старых добрых регулярных выражений, обрабатываемых клиентом (и если данные не могут покинуть базу данных, используя сборки CLR для передачи функциональности регулярного выражения в SQL)?
@DanielBlais См. Абзац, который начинается с «Что мне нужно увидеть в этой выходной таблице…» (я разбил его на отдельный абзац, чтобы сделать его более очевидным).
Я копирую-вставляю-запускаю ваш код и получаю ожидаемый результат. 1 = 6720-999-0000 и 2 = 6720-999-0404.
@DanielBlais Результат, который мне нужен для ID 1, - 6720-010-0000, а не 6720-999-0000. Есть два разных строковых значения замены, и для всех замен используется только последнее.
Проблема в том, что выберите @cmd = N'update ... сохранит последнюю команду обновления в @ cmd. Когда вы запустите этот запрос, он будет основан на последнем результате. Вы должны использовать другую логику или использовать курсор.
Это работает, но не оптимально с точки зрения производительности:
SET NOCOUNT OFF
if object_id(N'tempdb..#TimeData') is not null drop table #TimeData
create table #TimeData(ID int, expacct varchar(45), glseg1 varchar(10), glseg2 varchar(10))
insert into #TimeData
values(1, '6720-000-0000', '010', '')
, (2, '6720-999-0000', '030', '0404')
select * from #TimeData
-- create table of all possible account numbers from ALL_TimeCardDetail with their associated replacement strings
if object_id(N'tempdb..#AccountNumbers') is not null drop table #AccountNumbers
create table #AccountNumbers(id int identity, Original varchar(100), ReplacementString nvarchar(max), Updated varchar(100))
insert into #AccountNumbers (original, ReplacementString, updated)
values('6720-000-0000', 'substring(''@Acct'',1,4) + ''-'' + rtrim(ltrim(isnull(nullif (d.glseg1, ''''), substring(''@Acct'',6,3)))) + ''-'' + rtrim(ltrim(isnull(nullif (d.glseg2, ''''), substring(''@Acct'',10,4))))', '')
, ('6720-999-0000', 'substring(''@Acct'',1,4) + ''-'' + substring(''@Acct'',6,3) + ''-'' + rtrim(ltrim(isnull(nullif (d.glseg2, ''''), substring(''@Acct'',10,4))))', '')
select * from #AccountNumbers
declare @cmd nvarchar(max)
declare @id int
declare c cursor for select id from #AccountNumbers
open c
fetch next from c into @id
while @@FETCH_STATUS = 0
begin
select @cmd = N'update #TimeData set expacct = ' + replace(n.ReplacementString, '@Acct', d.expacct) + '
from #TimeData d
inner join #AccountNumbers n
on d.expacct = n.Original
where n.id = ' + convert(varchar, @id)
from #TimeData d
inner join #AccountNumbers n
on d.expacct = n.Original
where n.id = @id
print @cmd
exec (@cmd)
fetch next from c into @id
end
close c
deallocate c
select * from #TimeData
Привет. Я не уверен, что вы пытаетесь сделать. Если я хорошо понимаю, 010 нужно заменять ничем, а 030 на 0404? Но вы не использовали 010 или 030 в своем примере! Выложите, пожалуйста, ожидаемый результат.