May 22, 2014 at 11:16 am
I have the following view
CREATE VIEW [dbo].[VW_QUERY1]
AS
SELECT t1.* FROM TABLE1 t1 INNER JOIN TABLE2 t2
on t1.key1 = t2.key1
WHERE t2.code > 5
I need to run this view in all the datatabases, about 50 of them and return the result in one resultset.
What is the most efficient way of doing it? I was thinking of writing a cursor in the view but wondering if that is the right way.
Thank you
May 22, 2014 at 11:34 am
You can't have a cursor in a view.
Maybe a stored proc, loop over the databases, insert the results into a temp table, query the temp table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
May 22, 2014 at 12:18 pm
How about using a cursor and a CTE in a view and doing a final select on the CTE in a view? Just thought of it.
May 22, 2014 at 12:26 pm
Guras (5/22/2014)
How about using a cursor and a CTE in a view and doing a final select on the CTE in a view? Just thought of it.
erm - you can't have a cursor in a view.
If you need to loop through a bunch of databases, write a stored procedure and do the looping there.
If it must be in a view, then you may need to do a bunch of unions.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
May 22, 2014 at 12:31 pm
Unfortuantely I have almost 50 databases and it has to be a view since the client is using the view to do a data lookup.
May 22, 2014 at 12:34 pm
Then you will need to utilize a bunch of unions to get all of the database information.
An alternative is you could prepopulate a table with all of the data for this query and then just expose that table for them to view the data.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply