Sql Server print affected rows during while loop
I have a stored procedure running on sql server 2008 looping over a table of ~50 million rows, deleting records one day at a time (approx. 25,000 records a day). I want to be able to watch this occur as the process runs via print statements to the messages window. I'd like to be able to see a message each time a day's worth of deletions is committed. Is there any way to do something like this? The pr开发者_开发问答ocedure is roughly laid out like this:
WHILE EXISTS(<query statement>)
BEGIN
BEGIN TRAN
DELETE
FROM <table>
WHERE <condition>
AND <condition>
--need some sort of "rows affected" statement here
COMMIT
END
Unfortunately, PRINT statement output is spooled up and not written to the output immediately. A way round this is to use RAISERROR like this:
RAISERROR ('MY MESSAGE', 0, 1) WITH NOWAIT
Specifically for what you want:
DECLARE @Msg VARCHAR(200)
WHILE EXISTS(<query statement>)
BEGIN
BEGIN TRAN
DELETE
FROM <table>
WHERE <condition>
AND <condition>
SELECT @Msg = CAST(@@ROWCOUNT AS VARCHAR(10)) + ' rows affected'
RAISERROR (@Msg, 0, 1) WITH NOWAIT
COMMIT
END
I also tend to include the current time in the message so I can record progress against time.
Anything more complex than select @@rowcount ?
How about using the OUTPUT clause?? Can you write to the SQL Server console from your stored proc??
Like in a trigger, you have a "Deleted" pseudo-table at your hands, which contains the rows that are being deleted, with all their columns.
WHILE EXISTS(<query statement>)
BEGIN
BEGIN TRAN
DELETE FROM <table>
OUTPUT deleted.ID, deleted.TimeStamp -- or whatever
WHERE <condition>
AND <condition>
COMMIT
END
I used to do something similar to this, and because prints where spooled up it was not much use, however I discovered that the command line utility osql
outputs them immediately, so I wrote the stored proc and called it from a batch file instead, which let me see the progress.
you could do something like this to iterate over each row
declare @ii (id int) table
-- for batchchunk operations
declare @numTransactions as int = 0,
@totalRowsInserted as int = 0,
@chunksize int = 0,
@printnow varchar(1000)
--does the initial insert into the table
select * from #table
SET @numTransactions =@@rowcount
SET @printnow = 'Should Insert :'+cast(@numTransactions as varchar)+ ' rows into the table.'
exec db.printNow @printnow; -- shortened proc
BEGIN
INSERTCHUNKS:
SET ROWCOUNT @Batchsize
insert into table
(
type_id,
parent_id,
created_by_user_id,
created_at,
updated_by_user_id,
updated_at,
created_by_system_id,
updated_by_system_id,
is_deleted
)
output inserted.id into @ii(id)
select
1,--party_type_id
NULL,--parent_party_id
@user_id, --created_by_user_id
case when created_at is NULL then getdate() else created_at end ,
NULL , --updated_by_user_id
case when updated_at is NULL then getdate() else updated_at end, --
updated_at
case when created_by_system_id is null then 292 else 0 end, --created_by_system_id
updated_by_system_id,--updated_by_system_id \
0 --is_deleted
from
#table
order by id asc
OFFSET
@TotalRowsInserted ROWS
FETCH NEXT @batchsize ROWS ONLY
set @chunksize =@@rowcount
IF @chunksize > 0
BEGIN
set @totalRowsInserted = @totalRowsInserted + @chunksize
SET @printnow = 'Batch done: ' +
cast(@chunksize as varchar) + ' rows in Batch ; '
+ cast(@totalRowsInserted as varchar) + ' total inserted so far
into the table.' exec db.printnow @printnow;
waitfor delay '00:00:01'
GOTO INSERTCHUNKS
END
SET ROWCOUNT 0;
END
Do the following:
DECLARE @queryCount AS INT, @start AS INT, @stop as INT, @table as varchar(max)
SELECT @start = cnt(*) as a FROM <table>
SELECT @queryCount = cnt(*)
FROM <table>
WHERE <condition>
AND <condition>
DELETE
FROM <table>
WHERE <condition>
AND <condition>
SELECT @stop = cnt(*) as a FROM <table>
PRINT(convert(varchar(max), @queryCount) + ' rows deleted from ' + convert(varchar(max), @table))
If you want to validate, you can throw in the following code.
PRINT('Rows at start: ' + convert(varchar(max), @start))
PRINT('Rows at end: ' + convert(varchar(max), @stop))
PRINT('Row difference: ' + convert(varchar(max), @start - @stop))
Caveat: If the database can affect rows while this is going on, you may see discrepancies.
精彩评论