49

I run a lot of queries that perform INSERT's, insert SELECT's, UPDATE's and ALTER's on tables, and when developing these queries, the intermediate steps that are run to test that various parts of the query work, potentially change the table or the data within the table.

Is it possible to do a dry run of a query and have SQL Management Studio give you what the results would be, without actually modifying the data or the table structure?

At the moment I have to back up the database, and run the query. If it works, good, if it doesn't, I have to restore the database (which can take around a hour) and I'm trying to avoid wasting all this time having to restore databases.

Null
  • 1,950
  • 9
  • 30
  • 33
bizzehdee
  • 20,289
  • 11
  • 46
  • 76
  • I think you could use temporary tables for that you want. For instance, create a temporary table, then insert data to it. Finally update it and alter it if you want. After all your work you could drop the tables you will have created. In order to be more specific in what I wrote, please give us your scenario of insert etc. and your tables. – Christos Nov 07 '13 at 13:54

2 Answers2

97

Use an SQL transaction to make your changes then back them out.

Before you execute your script:

BEGIN TRANSACTION;

After you execute your script and have done your checking:

ROLLBACK TRANSACTION;

Every change in your script will then be undone.

Note: Make sure you don't have a COMMIT in your script!

Bohemian
  • 412,405
  • 93
  • 575
  • 722
  • 8
    Just a comment: If there are any `IDENTITY` columns then the rollback will cause values to be "skipped". This is to be expected. – HABO Nov 07 '13 at 14:17
  • Pretty much exactly what i was looking for. Thank you – bizzehdee Nov 07 '13 at 14:36
  • 1
    what do you mean by `Make sure you don't have a COMMIT in your script!` – PositiveGuy Dec 01 '20 at 23:20
  • 1
    @PositiveGuy the idea here is to run the script, check that the result looks OK and if not rollback. Otherwise commit. If you have a commit in the script it’s done no matter what you think of the result. – Bohemian Dec 02 '20 at 10:52
  • 2
    It should be noted that reviewing changes within a transaction requires `WITH (NOLOCK)` table hint. – montonero Sep 27 '21 at 12:45
8

Begin the transaction, perform the table operations, and rollback as shown below:

BEGIN TRAN

UPDATE  C
SET column1 = 'XXX'
FROM table1 C

SELECT *
FROM table1
WHERE column1 = 'XXX'

ROLLBACK TRAN

This will rollback all the operations performed since the last commit since the beginning of this transaction.

Infinite Recursion
  • 6,511
  • 28
  • 39
  • 51
chami007
  • 159
  • 3
  • 2
  • 3
    Always write some description how your answer can be helpful to solve op problem – Ajay S Oct 17 '14 at 16:52
  • 3
    I have added the English description in this, please feel free to roll back if you disagree. In all future questions and answers, please describe in English, code dumps are not useful. Good luck. – Infinite Recursion Oct 17 '14 at 16:58
  • 1
    TRAN is not a valid syntax – PositiveGuy Dec 01 '20 at 23:18
  • 3
    @PositiveGuy TRAN does appear to be valid syntax, at least for MS SQL (sql-server is tagged in the question): https://learn.microsoft.com/en-us/sql/t-sql/language-elements/begin-transaction-transact-sql?redirectedfrom=MSDN&view=sql-server-ver15 says Syntax starts BEGIN { TRAN | TRANSACTION } – David Thompson Feb 03 '21 at 16:20