I'm trying to use a dynamic SQL statement to generate my Delete statements for a set of tables. I can generate the Delete statements without any problems. What I need is to be able to generate an additional comment line showing how many rows will be affected by the statement. I need to submit my SQL to our DBA's to have them run it on our production server and they like to have a comment(or PRINT statement) of how many rows will be affected. Something like this:
DELETE FROM dbo.ARS_CI_History WHERE BatchId = 000;
-- (10 row(s) affected)
I have about 25 tables I need to do this for. While I can just run a select or count for each to get the number of rows affected and paste it to create the above, I was just wondering if there was an easier way. This code would only need to be ran by me occasionally when I need to reload certain data.
I have this code to generate some of this:
SELECT 'SELECT COUNT(*) FROM dbo.' + [name] + ' WHERE BatchId = 000' + ';' AS SQLCount,
'DELETE FROM dbo.' + [name] + ' WHERE BatchId = 000' + ';' AS DeleteCommand
FROM sys.tables
WHERE [name] LIKE 'ARS%'
;
I would like to be able to execute the SQL in the SQLCount column to generate the comment like dynamically for each table .
Is this possible?
Thanks in advance for your input.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
February 10, 2020 at 9:09 pm
I think cursors will be your friend here.
Do a cursor with your select from above and store both into variables then loop through the cursor to execute both SQL statements and then fetch the next values and repeat.
Things to watch out for though - SQL Injection. If someone knew this process, and they have permissions to create tables, they could exploit the dynamic SQL. To get around that, you could toss in square brackets around "name" so you end up with something like
'SELECT COUNT(*) FROM [dbo].[' + [name] + '] WHERE BatchId = 000;'
Again, isn't 100% fool proof, but less likely to have SQL injection attack there.
With it only being 25 tables, I personally would be more tempted to use the query you have and copy-paste things into SSMS before running it. Not sure your DBA's will be happy with a dynamic SQL that deletes values from a table.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 10, 2020 at 9:26 pm
Sorry, I was in a hurry to get this out before a meeting. I don't want the dynamic SQL statement to be stored or used outside of me trying to generate the Delete statements with the number of rows that will be affected. I will not and can not run it on our production server. This is again just to generate the statements I can then give the DBA, the DBA will then run the actual Delete statements that are generated by this. I've already set up the SQL statements in SSMS and manually added the comment statements. I know at some point I may need to do this again, either on the same tables but different batch. Or I may need to do it on another set of tables. I was trying to find a short cut to generate the Delete SQL statements with the appropriate comment line with the exact number of rows that will be affected. I'll see if I can find a way to use the cursor.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
With a cursor that should be pretty easy to do.
Something along these lines:
DECLARE @dynamicCount VARCHAR(MAX)
DECLARE @dynamicDelete VARCHAR(MAX)
DECLARE @tblResult TABLE (ID INT IDENTITY(1,1), [Query] VARCHAR(MAX) )
DECLARE cur CURSOR LOCAL FAST_FORWARD
FOR
SELECT 'SELECT ''-- Number of affected rows: '' + CAST(COUNT(*) AS VARCHAR(25)) FROM dbo.' + [name] + ' WHERE BatchId = 000' + ';'
,'DELETE FROM dbo.' + [name] + ' WHERE BatchId = 000' + ';' AS DeleteCommand
FROM sys.tables
WHERE [name] LIKE 'ARS%'
OPEN [cur]
FETCH NEXT FROM cur INTO @dynamicCount, @dynamicDelete
WHILE @@FETCH_STATUS = 0
BEGIN
INSERT INTO @tblResult ([Query])
EXEC (@dynamicCount)
INSERT INTO @tblResult ([Query])
VALUES (@dynamicDelete)
FETCH NEXT FROM cur INTO @dynamicCount, @dynamicDelete
END
SELECT
[Query] FROM @tblResult
ORDER BY ID
Not the fastest or the prettiest code, but that will give you the row count followed by the TSQL to delete those rows. If you want the DELETE statement followed by the number of affected rows, just swap the INSERT statements. If you want the fastest query, you like don't want to use a cursor. But using the above, you can copy-paste from the result table variable to get the results you want.
The above is all just my opinion on what you should do.
As with all advice you find on a random internet forum - you shouldn't blindly follow it. Always test on a test server to see if there is negative side effects before making changes to live!
I recommend you NEVER run "random code" you found online on any system you care about UNLESS you understand and can verify the code OR you don't care if the code trashes your system.
February 10, 2020 at 10:18 pm
Thanks Brian, this works. And for what I need this for speed isn't an issue. Just saving me time from cutting and pasting or typing.
-------------------------------------------------------------
we travel not to escape life but for life not to escape us
Don't fear failure, fear regret.
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply