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.
"...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 :-)
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.
18 People are following this question.
