question

Richard_U avatar image
Richard_U asked

How can I create a series of tables from distinct results of a query

Probably a very simple question, but it's been almost a decade for me. I have a table that I need to break up into tables by country. I know I get the set of country codes I need from a Query like this" Select distinct Country_Code from AllCountries Then what I need to do is iterate through the result set, and create a series of tables for each country in the results of that query I know I need a select into statement, and then name the new table with the value in the country code, concatenate "_TBL" to the name and repeat until I get the last of the country codes. Any help would be much appreciated. I'm very rusty, thanks to have been being out of action for a while.
tablesquery-resultsquery-
2 comments
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

"...concatenate "_TBL" to the name..." Slightly off-topic, but you probably don't want to do that. It's known as "tibbling" and there are many reasons why it's a bad idea. Essentially, you don't want to be using an object name as a metatdata repository. The day will come when your access to the data is best achieved by say, a view or an ILTVF, rather than the base table. Then what? Lots of unnecessary refactoring. Or views with "tbl" in the name, tables with "vw" in the name. And don't get me started on "fn" functions :-)
1 Like 1 ·
Back on topic now... Can you give us more of an idea of business problem behind your question? There may be alternative ways solve it. I say this merely because it seems like an unusual request.
0 Likes 0 ·

1 Answer

· Write an Answer
dleyden avatar image
dleyden answered
Yeah it sounds like a bad idea. Why would you want a separate table for each country when you can just put keep a country attribute in one table if you have to stop users from one country seeing results from another country accomplish this using views or functions. But if you must do this then I think you will need to use dynamic sql inside a while loop.
10 |1200 characters needed characters left characters exceeded

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.