Вставьте «Удалено» в таблицу #temp, чтобы получить количество удаленных записей в цикле while, удаляя в пакетном режиме
Я пытаюсь получить количество удаленных записей в сценарии, в котором я удаляю строки из многих таблиц, чтобы очистить данные из-за недостаточного места на диске.
Удаление происходит партиями по 500, поскольку таблица содержит миллионы записей.
WHILE (1 = 1)
BEGIN
SELECT * INTO #tempHistTable1 FROM DatabaseABC..HistTable1 WHERE 0=1
DELETE DatabaseABC..HistTable1
OUTPUT DELETED.* INTO #tempHistTable1
WHERE ExtractionDate < ='2016-12-31'
Select @qry = 'Select ''Total Number of records deleted from HistTable1 = '' + CONVERT(VARCHAR(20), COUNT(1)) from #tempHistTable1 '
---- Send the e-mail Total Count of Deleted Records
EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]',
@query = @qry,
@subject = 'Records Deleted in HistTable1'
IF @@ROWCOUNT = 0
BREAK;
END
WHILE (1 = 1)
BEGIN
SELECT * INTO #tempHistTable2 FROM DatabaseABC..HistTable2 WHERE 0=1
DELETE DatabaseABC..HistTable2
OUTPUT DELETED.* INTO #tempHistTable2
WHERE ExtractionDate < ='2016-12-31'
Select @qry = 'Select ''Total Number of records deleted from HistTable2 = '' + CONVERT(VARCHAR(20), COUNT(1)) from #tempHistTable2 '
---- Send the e-mail Total Count of Deleted Records
EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]',
@query = @qry,
@subject = 'Records Deleted in HistTable2'
IF @@ROWCOUNT = 0
BREAK;
END
Как этого добиться > отправить количество удаленных записей? С помощью приведенного выше кода я получаю сообщение об ошибке о том, что таблица #temp уже существует (я знаю причину этого в том, что когда цикл while выполняет следующий пакет - таблица #temp уже существует). Но как я могу получить общее количество удаленных записей, а затем отправить его по электронной почте?
Сможет ли sp_send_dbmail вообще получить доступ к таблице #tempHistTable1, созданной в контексте текущего spid? Я ожидаю, что вы получите расплывчатую и бесполезную ошибку: Сообщение 22050, уровень 16, состояние 1, строка 0. Не удалось инициализировать библиотеку sqlcmd с номером ошибки -2147467259.
Вам следует создать (select * info #temp....) временную таблицу вне цикла while.
Если единственной целью временной таблицы является получение количества строк, не беспокойтесь. Просто выполните удаление, а затем присвойте @@rowcount переменной в следующем операторе и используйте значение этой переменной — действительно ли вы хотите отправлять электронное письмо для каждой партии из 500 удаленных строк?
В настоящее время ваш IF @@ROWCOUNT = 0 не работает, так как проверка не происходит сразу после DELETE и, кстати, она будет сброшена.
Ответы 1
У вашего скрипта есть некоторые проблемы, как насчет этой версии:
DECLARE @count INT
, @totalcount INT
, @qry NVARCHAR(MAX)
SELECT *
INTO #tempHistTable1
FROM DatabaseABC..HistTable1
WHERE 0=1
SET @totalcount = 0
WHILE 1 = 1
BEGIN
DELETE TOP(500) DatabaseABC..HistTable1
OUTPUT DELETED.*
INTO #tempHistTable1
WHERE ExtractionDate < ='2016-12-31'
--
SET @count = @@ROWCOUNT
IF @count = 0
BREAK;
SET @totalcount = @totalcount + @count
SELECT @qry = 'Select ''Total Number of records deleted from HistTable1 = ' + CAST(@totalcount AS NVARCHAR(10)) + ''''
---- Send the e-mail Total Count of Deleted Records
EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]',
@query = @qry,
@subject = 'Records Deleted in HistTable1'
END
SELECT *
INTO #tempHistTable2
FROM DatabaseABC..HistTable2
WHERE 0=1
SET @totalcount = 0
WHILE 1 = 1
BEGIN
DELETE TOP(500) DatabaseABC..HistTable2
OUTPUT DELETED.*
INTO #tempHistTable2
WHERE ExtractionDate < ='2016-12-31'
--
SET @count = @@ROWCOUNT
IF @count = 0
BREAK;
SET @totalcount = @totalcount + @count
SELECT @qry = 'Select ''Total Number of records deleted from HistTable2 = ' + CAST(@totalcount AS NVARCHAR(10)) + ''''
---- Send the e-mail Total Count of Deleted Records
EXEC msdb.dbo.sp_send_dbmail
@recipients = '[email protected]',
@query = @qry,
@subject = 'Records Deleted in HistTable2'
END
- На самом деле этот пакет объединяет 500 строк.
- Правильно выпрыгивает на финише
Если вам не нужно сохранять старые строки, вы можете удалить всю вещь SELECT INTO/OUTPUT DELETED, это должно ускорить ваше выполнение.
Спасибо @Siggermannen. Теперь я знаю, где допустил ошибку. Но разве этот код не будет отправлять электронное письмо для каждой партии из 500 писем? Разве код sp_send_dbmail не должен находиться за пределами циклов while?
Я следовал вашему первоначальному сценарию и думал, что вы хотите постепенно писать по электронной почте. Но да, если вы хотите отправить его только один раз, просто переместите его.
Просто создайте временную таблицу заранее и вставьте в нее, а не выбирайте в ней.