I have 50 datasets. Each dataset has different records of 3 variables each. I want to compile one dataset that all records from 50 datasets for those 3 variables.

Example is Table 1:

I have 50 datasets. Each dataset has different records of 3 variables each. I want to compile one dataset that all records from 50 datasets for those 3 variables.

Example is Table 1:

Table 2:

Table 3:

I have 50 such tables and I want end result as:

I assigned table 1 as my final table and tried inserting into other tables.

insert into final_table
select var1,var2,var3 from table2

Is there a way for me to repeat this operation without having to copy and paste this 50+ times?

• sql
• sql-insert
• dbvisualizer

• Unfamiliar with DBvisualizer. Can you UNION 50+ queries for a single insert? If so then the query would take less than one minute to write. – Isolated Commented Feb 6 at 21:11
• I think union might be the correct operation but I have been searching and wondering if I need dynamic sql and will it even work in DBV. – user10969476 Commented Feb 7 at 1:52
• 1 Please tag which database type you are asking about. I think this is more of a database question than a DBMS client question. I think what you you want is called UNION ALL (includes duplicates); there are lots of guides on the topic, example – vakio Commented 2 days ago
• Please mention 1. which database server you are connecting to, and 2. how you determine the source table names (are they "all tables whose name start with some given prefix", or "the entries of a metatable containing the name of the tables to aggregate"?). Your need is not doable in standard SQL, but it is using introspection and query-generating queries. However those are specific to the server kind, thus our need to know it. – Guillaume Outters Commented 2 days ago

1 Answer 1

Some artisanal solutions, from less to more automatized;
depending on the frequency you need to execute it, maybe a fully automatized solution is not worth it.

With a spreadsheet

In a spreadsheet where you paste the table names into column A, put the following formula in B1 then extend formula to the bottom:

="insert into final_table select var1, var2, var3 from "&A1&";"

Then copy-paste column B to DBVisualizer and run them.

N.B.: you can do this with any scripting language you're at ease with.

With copy-paste in DBVisualizer

Same as with a spreadsheet, except the formula is done in SQL.
But see my comment, we need to know your DB engine and table structure to be able to answer.

In 1 consolidated instruction in the DB

See my comment too, we have to know the DB server and table structure.