I think we have all been there.
You have been given data spread across several tables. You need to combine or merge these tables into one to perform your analysis.
I'll show you three ways to perform this task.
For this demo, I have three tables, all with the same table structure, which certainly makes merging them easier.
The first step in this process is typically to create the new table that will contain the merged tables. You can do this 1) by creating a copy of one of the input tables or 2) by creating a new table based on the table structure of one of the input tables.
If you create a copy of one of the input tables, remember which one you used to avoid inserting this table again.
Happy #MapInfoMonday!
The Manual Way Using Append Rows
Having only a few tables to merge, the manual process may be the easiest and fastest.
I start by creating a copy of one of the tables so that I can use this to insert the other tables into.
From the Table List I right-click on the table and use Save Copy As ... to create the copy.
I specify the name of the new table in the Save Copy of Table As dialog and also check the option Open new able using view to get the table opened.
Now, with the new table open, I can use Append Rows to insert the other tables. From the Table tab, I click on Append Rows.
In the Append Rows to Table dialog, I select the Append Table to add and the to Table to add it to.
When you click the OK button, the table will be inserted into the other table.
That was one table added. Now you have to use the Append Rows to Table dialog for the other tables to add. In my example, I only have to use it for one more table.
If you have more than five tables, this quickly becomes cumbersome.
There is also a risk that you accidentally insert one table twice. Trust me, I have done it myself.
Is there a smarter way? One that removed some of the manual steps where you may do something wrong?
Yes, there is. You can use MapBasic to automate part of the work.
This alternative method uses the same process as described above. We will, however, automate the last step of inserting one table into another.
As I always have the MapBasic window open in MapInfo Pro, I can find the statement used to insert one table into another:
Insert Into metropolitan_street_Q1_2023
(Crime_ID, Month, Reported_by, Falls_within, Longitude, Latitude, Location, LSOA_code, LSOA_name, Crime_type, Last_outcome_category, Context)
Select Crime_ID, Month, Reported_by, Falls_within, Longitude, Latitude, Location, LSOA_code, LSOA_name, Crime_type, Last_outcome_category, Context
From _2023_02_metropolitan_street DropIndex Auto
Now that the table structures of the tables are identical, I can simplify this a bit so that it will work when merging other tables with identical table structures but different from the above:
Insert Into metropolitan_street_Q1_2023
Select *From _2023_02_metropolitan_street DropIndex Auto
If I only have a few tables to merge, I can copy this statement and modify the table names:
Insert Into metropolitan_street_Q1_2023
Select *From _2023_01_metropolitan_street DropIndex Auto
Insert Into metropolitan_street_Q1_2023
Select *From _2023_02_metropolitan_street DropIndex Auto
If I have many tables, this gets cumbersome. I prefer a different method. The idea is to use the list of open tables. As you saw above, I started with the tables to merge open. This means these tables are first in the list of open tables.
I then created a copy of one of these tables and opened that table. This table is the last in the list of open tables.
If you created a new table based on one of the input tables, you are good. If you made a copy of one of the input tables, you should close that now to avoid inserting it again.
MapBasic allows me to query tables by their name or their number in the list of open tables. I can use this to get to the first and last table name. This is done via the TableInfo() function. The MapBasic function NumTables() tells you the number of open tables.
With this information, I can now modify the Insert statement above to this:
Insert Into TableInfo(NumTables(), TAB_INFO_NAME)
Select *From TableInfo(1, TAB_INFO_NAME) DropIndex Auto
This works fine the first time. But for it to also insert the next table, I need to remove the first table from the list of open tables. That is done simply by closing the table.
Insert Into TableInfo(NumTables(), TAB_INFO_NAME)
Select * From TableInfo(1, TAB_INFO_NAME) DropIndex Auto
Close Table TableInfo(1, TAB_INFO_NAME)
You can run the two statements from the SQL window or the MapBasic window.
You must execute the script manually as many times as you have tables to insert. This goes for running the script in the SQL window and through the MapBasic window.
In the SQL Window, you execute the statements via the Run button.
In the MapBasic window, you must select all the statements and then hit the Enter key.
If you run the script through the SQL window, you can add a statement at the beginning to avoid running the script too many times:
End Program Condition(NumTables() = 1)
The statement above stops executing the statement if you only have 1 table left open.
In a follow-up article, I will show you how to automate this process completely through Python. Keep watching this space!
------------------------------
Peter Horsbøll Møller
Principal Presales Consultant | Distinguished Engineer
Precisely | Trust in Data
------------------------------