0

I'm just getting started learning SQL Server Management Studio. I'm familiar with building nested queries in Access, e.g.:

Query1 (get a data set)

select t1.a,t2.b,t1.abc,t2.def from tbl_FNA t1
inner join
tbl_DMZ t2 on
t1.b=t2.b

Query2 (return only the rows from Query1 with the minimum value of b)

select q1.* from Query1 q1
inner join
(select a,min(b) as min_b from Query1 group by a) q2
on
q1.a=q2.a
and
q1.b=q2.min_b

This makes it easier to debug the code because if there's something wrong with Query1, I only have to change it in one place instead of 3 places.

I'm aware that if I have write access to the database, I can create views and/or stored procedures that can help with this. But in this case, I have read-only access.

Is there a way in SSMS that I can save a query and then refer to that query by name inside another query?

sigil
  • 9,370
  • 40
  • 119
  • 199

1 Answers1

1

You can do this inside the 'session' using a CTE, but without write access you can't save any objects to the database.

WITH q2 AS
    (select a,min(b) as min_b from Query1 group by a)

select q1.* from 
Query1 q1
inner join
q2
on
q1.a=q2.a
and
q1.b=q2.min_b

SELECT * FROM q2

SELECT TOP 1000 * FROM q2
Nick.Mc
  • 18,304
  • 6
  • 61
  • 91
  • YOU can also create custom templates (View / Template Explorer) and use this to save handily accessible bits of code if you want. – Nick.Mc Mar 11 '14 at 22:37
  • Looking at the Template Explorer, does it matter where I save the CTE for purposes of making it accessible to my queries? – sigil Mar 11 '14 at 23:35
  • sorry, I mean save the custom template – sigil Mar 11 '14 at 23:37
  • The only way a CTE defined in a template will be accessible to your queries is if you copy the template into your SMSS query window. A template is just snippet of text. It's not an actual referenceable database object. You cannot save a reusable object to your database without sufficient access. – Nick.Mc Mar 12 '14 at 00:25
  • A workaround is to create a new database that you do have access to. You can save all of your objects in here and then reference across databases. You could even create a 'replica' of your source database in your own sandbox database by creating synonyms to objects in the source database. DO you have an option to create a database on the same server? – Nick.Mc Mar 12 '14 at 00:26
  • Yes, there is another database, although it would be a bit complicated to set up the connection to it for using these objects. I guess I was hoping to be able to save a query as a `.sql` file, then somehow reference it in another query by using that filename, instead of having to copy it into a CTE every time I want to reference it. Not possible? – sigil Mar 12 '14 at 00:42
  • I'm not aware of a way to reference and external .SQL file directly. I'm not sure what you mean about 'a bit complicated...' If you update your question with more details about your access to a different database perhaps I can help further. – Nick.Mc Mar 12 '14 at 00:51