CODEWITHSUNDEEP
sql-serversql-server-2008sql
Iceman
Iceman

Reputation: 335

SQL - update, delete, insert - Whatif scenerio

I was reading an article the other day the showed how to run SQL Update, Insert, or Deletes as a whatif type scenario. I don't remember the parameter that they talked about and now I can't find the article. Not sure if I was dreaming.

Anyway, does anyone know if there is a parameter in SQL2008 that lets you try an insert, update, or delete without actually committing it? It will actually log or show you what it would have updated. You remove the parameter and run it if it behaves as you would expect.

Upvotes: 1

Views: 5414

Answers (6)

Vince
Vince

Reputation: 1

I'm more than seven years late to this particular party but I suspect the feature in question may also have been the OUTPUT clause. Certainly, it can be used to implement whatif functionality similar to Powershell's in a t-sql stored procedure.

https://learn.microsoft.com/en-us/sql/t-sql/queries/output-clause-transact-sql

Use this in each insert/update/delete/merge query to let the SP output a meaningful resultset of the changes it makes e.g. outputting the table name and action performed as the first two columns then all the altered columns.

Then simply rollback the changes if a @whatif parameter is set to 1 or commit them if @whatif is set to 0.

Upvotes: 0

Iceman
Iceman

Reputation: 335

OK, finally figured it out. I've confused this with another project I was working on with PowerShell. PowerShell has a "whatif" parameter that can be used to show you what files would be removed before they are removed.

My apologies to those who have spent time trying to find an answer to this port and my thanks to those of you who have responsed.

Upvotes: 4

ercan
ercan

Reputation: 1716

Toad for SQL Server has a "Validate SQL" feature that checks queries against wrong table/column names etc. . Maybe you are talking about some new feature in SSMS 2008 similar to that...

Upvotes: 0

Alex K.
Alex K.

Reputation: 175866

Do you perhaps mean SET NOEXEC ON ?

When SET NOEXEC is ON, SQL Server compiles each batch of Transact-SQL statements but does not execute them. When SET NOEXEC is OFF, all batches are executed after compilation.

Note that this won't warn/indicate things like key violations.

Upvotes: 0

Sathyajith Bhat
Sathyajith Bhat

Reputation: 21851

I believe you're talking about BEGIN TRANSACTION

BEGIN TRANSACTION starts a local transaction for the connection issuing the statement. Depending on the current transaction isolation level settings, many resources acquired to support the Transact-SQL statements issued by the connection are locked by the transaction until it is completed with either a COMMIT TRANSACTION or ROLLBACK TRANSACTION statement. Transactions left outstanding for long periods of time can prevent other users from accessing these locked resources, and also can prevent log truncation.

Upvotes: 1

David Spillett
David Spillett

Reputation: 1421

I don't know of a SQL2008 specific feature with any SQL service that supports transactions you can do this:

  1. Start a transaction ("BEGIN TRANSACTION" in TSQL)
  2. The rest of your INSERT/UPDATE/DELETE/what-ever code
  3. (optional) Some extra SELECT statements and such if needed to output the result of the above actions, if the default output from step 2 (things like "X rows affected") is not enough
  4. Rollback the transaction ("ROLLBACK TRANSACTION" in TSQL)
  5. (optional) Repeat the testing code to show how things are without the code in step 2 having run

For example:

BEGIN TRANSACTION
-- make changes
DELETE people WHERE name LIKE 'X%'
DELETE people WHERE name LIKE 'D%'
EXEC some_proc_that_does_more_work
-- check the DB state after the changes
SELECT COUNT(*) FROM people
-- undo
ROLLBACK TRANSACTION
-- confirm the DB state without the changes
SELECT COUNT(*) FROM people

(you might prefer to do the optional "confirm" step before starting the transaction rather than after rolling it back, but I've always done it this way around as it keeps the two likely-to-be-identical sections of code together for easier editing)

If you use something like this rather then something SQL2008 specific the technique should be transferable to other RDBS too (just update the syntax if needed).

Upvotes: 5

Related Questions