SQL Server - Query tool to audit results prior to running query?

My scenario is I have two SQL scripts that are very similar and are used to insert values into setup tables.

The only difference between the two scripts is the values that are inserted into the tables.  One sript is for a staging environment and the other is for a production environemnt.

Ideally, I would like a tool that would compare the two queries and highlight any differences in the syntax.

The other thought I had was some way to run a "mock" run of the production script to a file or view without actually updating the database so I can ensure the results are correct prior to runnning.

How would you efficiently do this query comparison?  I'm not sure what built-in or add-on tools are available.

Thanks

JMO9966Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

David ToddSenior DBACommented:
Hi,

'tis easy - expand the scripts so the values to be inserted are held in a temp table or table variable, which can be output prior to the insert being called.

HTH
  David


-- example
-- First query

insert dbo.SomeTable(
	Col1
	, Col2
	, Col3
	)
	select
		ss.Col1
		, ss.Col2
		, ss.Col3 * 3.245
	from dbo.SomeOtherTable ss
	where
		SomeWhereCondition
;

-- Becomes
if object_id( N'tempdb..#t', N'U' ) is not null
	drop table #t;

create table #t(
	Col1 int
	, Col2 int
	, Col3 money
	)
;

insert #t(
	Col1
	, Col2
	, Col3
	)
	select
		ss.Col1
		, ss.Col2
		, ss.Col3 * 3.245
	from dbo.SomeOtherTable ss
	where
		SomeWhereCondition
;

select *
from #t
;

--== only run the next bit when sure you want to do the insert
insert dbo.SomeTable(
	Col1
	, Col2
	, Col3
	)
	select
		ss.Col1
		, ss.Col2
		, ss.Col3
	from #t
;

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
JMO9966Author Commented:
Thanks, I tried using similar syntax for a Create View with an insert statement was unsuccessful.

A sample of a SQL View with an Insert statement to preview the results would be great.
0
jogosCommented:
To keep it simple

Possible with BEGIN TRAN - ROLLBACK TRAN but that's not the kind of answer you wanted I think and not advisable on production, but perfectly on a restored copy of db.
0
David ToddSenior DBACommented:
Hi,

I'm not sure what you are trying to do with a view, so here are a few thoughts on views.

An insert can only insert to one table at a time. So generally inserting against a view doesn't get anything, and can become a maintenance issue down the track when you or a colleague forget or doesn't realise that it is a view and not a table and adds in a column that is in a second table ...

Views can only contain single select statements. Anything more complex requires a procedure.

HTH
  David
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.