In MS Studio how to SELECT from an existing SQL file

Question

I have a complicated query that select parts from an MRP table using several different criteria. The output of the query is a list of parts which includes the key field [PartNum]. I have saved this query to a file, for example let's says "MyQuery.sql".

I want to use the output of that query in another query, something like "SELECT * FROM [MyQuery] JOIN [part_stock] on [part_stock].[PartNum] = [MyQuery].[PartNum]". The idea is that I have this rather complicated selection criteria for extracting a list of parts from the database. I then want to run other queries on just those selected part numbers. I could just copy all the statements from the original query into the new queries, but that would make them overly complicated. Also, I may want to change the selection criteria for the parts, and I would prefer to only change it in one file and then just re-execute all the queries that use that list.

I realize that I could use the first query to create a table in the DB, and then have the other queries reference that table. But this is a live MPR system and I don't want to alter the DB schema (especially since I don't have much experience with SQL).

How can I use the output from one query file as the input to another query? If that's not possible, is there another way to accomplish this task without creating a table in the DB?

Answer 1

I do that a lot.

Yes I have my library of scripts, for instance, to check if the replication is healthy,

but it could also be just a query.

the way I do is - I use powershell.

basically this shows me all the scripts that are on a folder:

first you copy your file MyQuery.sql into this folder: C:\Users\My_name\OneDrive\scripts to add to repository\

this script will list all files in that folder:

  $path = "C:\Users\My_name\OneDrive\scripts to add to repository\"
  $sqlScripts = get-childItem -path $path
  $sqlScripts  

  foreach ($SQL in $sqlScripts){
    Write-Host $path$SQL

  }   

to run a script I use the following command:

Invoke-DbaQuery -SqlInstance $SQLConn  -File "C:\Users\My_name\OneDrive\scripts to add to repository\scripts to add to repository\MyQuery.sql"