1

Due to the way our database is stored, we have tables for each significant event that occurs within a products life:

  • Acquired
  • Sold
  • Delivered

I need to go through and find the status of a product at any given time. In order to do so I'd need to query all of the tables within the schema and find the record with the most up to date record. I know this is possible by union-ing all tables and then finding the MAX timestamp but I wonder if there's a more elegant solution?

Is it possible to query all tables by just querying the root schema or database? Is there a way to loop through all tables within the schema and substitute that into the FROM clause?

Any help is appreciated.

Thanks

Improve this question

1 Answer 1

Reset to default
1

You could write a Stored Procedure but, IMO, that would only be worth the effort (and more elegant) if the list of tables changed regularly. If the list of tables is relatively fixed then creating a UNION statement is probably the most elegant solution and relatively trivial to create - if you plan to use it regularly then just create it as a View.

The way I always approach this type of problem (creating the same SQL for multiple tables) is to dump the list of tables out into Excel, generate the SQL statement for the first table using functions, copy this function down for all the table names and then concatenate all these statements in a final function. You can then just paste this text back into your SQL editor

Improve this answer
Sign up to request clarification or add additional context in comments.

2 Comments

Yeah I've created a UNION statement that does the job and grabs the MAX timestamp. There is the potential that the tables will change regularly so I'll take a look into a stored procedure. Thanks for getting back to me!
I second this - and I also do the exact same thing with creating statements for multiple tables via concatenating Excel cells with functions :)

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.