0

I have a list of items (contained in a text file list.txt):

//list.txt
item1
...
itemN

I need to create a table in a database for each item:

table_item1
...
table_itemN

I don't want to do it manually, so I'm looking for a way to automate this. Can I somehow pass list.txt into sql-script and then create tables per each item using some SQL-loop statement? Something like this (pseudocode):

// script.sql
FOREACH item
  CREATE TABLE table_item

Could you please tell me how can I paramerize SQL command for creating a table? I need an SQL procedure with a parameter:

create_table(String table_title);
Daniel
  • 295
  • 2
  • 9
  • Of course it can be done, but I don't know if it will be meaningful. – jarlh Aug 21 '20 at 12:08
  • @jarlh Could you please explain how can I achieve this? And what are your concerns about this? – Daniel Aug 21 '20 at 12:25
  • You can probably do this with sqlcmd (for sql server), allowing you to write a script in something like powershell. Or the other way around, you can write a stored procedure/script file in SQL that acesses the filesystem and parses it and builds the statements through dynamic sql. But it's A going to be dependant on your DBMS (which you didn't tag), and B since table statements will become dynamic sql/sql in the form of strings, it's quite at risk for SQL injections and whatnot. – D Kramer Aug 21 '20 at 12:42
  • 1
    "I need to create a table in a database for each item:". That is a big mistake. You should put all items in a single table. – Gordon Linoff Aug 21 '20 at 13:27
  • @Gordon Linoff No, I need s separate table per each item, but I'm also going to have a table of all items. Could you please tell me how can I parametrise SQL script? – Daniel Aug 21 '20 at 13:33
  • 1
    If you _also_ have a table of all items, then having one table for each item is an even bigger mistake. What problem are you trying to solve with that approach? And please tell us which DBMS product you are using –  Aug 21 '20 at 13:37
  • @a_horse_with_no_name Let's say item_xyz have a parameter_xyz which is varying over time. So I'm just gonna store ` – Daniel Aug 21 '20 at 14:07
  • You should have a table `(time, name_of_parameter, value_of_parameter, primary_key_of_item)` –  Aug 21 '20 at 14:08
  • @a_horse_with_no_name I don't have any constraints about DBMS. I can use any SQL-compatible DB. – Daniel Aug 21 '20 at 14:08
  • @a_horse_with_no_name But why? Why is one huge table better than several average-size tables? – Daniel Aug 21 '20 at 14:14
  • 1
    Why would you want to deal with 10 million tables? Just image queries like "give me the parameter values for all items with IDs between 1 and 500" - that would require a query with **500** joins - hardly efficient. What if you delete an Item? You always have to remember to drop the table. What if you want to run aggregates? Again you need a query that includes potentially hundreds if not thousands of tables - that is going to be a nightmare for the optimizer (parsing the query might effectively become a bottleneck). This is simply not how relational databases work, and it doesn't scale –  Aug 21 '20 at 14:18
  • @a_horse_with_no_name Probably you're right. But I need a query "give me the latest saved parameter value for `item_xyz`". In my approach it's stored in the last row of the table `table_item_xyz`. How am I going to find the latest saved parameter value of `item_xyz` using your approach? – Daniel Aug 21 '20 at 15:08
  • `where item_pk = 'xyz'` –  Aug 21 '20 at 15:09
  • @a_horse_with_no_name I use `where item_pk = 'xyz'` to get the table of parameter values for `item_xyz`, right? Then I take the last row, right? Is your approach suitable for getting all the latest parameters values? (I need to analyse the latest values, can I request them efficiently if I don't know the exact time of last saving?) – Daniel Aug 21 '20 at 15:19
  • @Daniel . . . Databases are designed to work with fewer tables with lots of rows. They are not designed to work with zillions of tables with few rows. – Gordon Linoff Aug 21 '20 at 15:55
  • @Gordon Linoff But I'm gonna have just a few hundreds of tables (with thousands of rows), no more. – Daniel Aug 21 '20 at 16:51
  • @Daniel . . . A handful of tables with millions of rows is better. – Gordon Linoff Aug 21 '20 at 23:27
  • @ Gordon Linoff Maybe. But how am I supposed to query the latest saved values `value(item1),...,value(itemN)`? Do I need to know the time of last saving? Using my approach I just take the last row of each table (last row is the latest row obviously). – Daniel Aug 22 '20 at 10:58

0 Answers0