How to append rows of one table into another one with the same structure?

Question

question

Enrique Elizaga asked Jan 16, '18 martin.j answered Apr 2, '20

How to append rows of one table into another one with the same structure?

Is there a way to add rows of Table A to table B using SQL? Both have the same structure, (columns). I wouldnt want to use SELECT and add each column manually because my tables have many columns.

Below an image of my question.

Software Version:

FlexSim 18.0.0

tables sql queries

add.jpg (65.7 KiB)

Answer 1 · 2018-01-17T16:40:16Z

Arun Kr answered Jan 17, '18 Enrique Elizaga commented Jan 18, '18

Hi Enrique Elizaga,

Here's a solution using loops and SQL queries. Maybe a better solution is available and on searching through the user manual, couldn't find UNION type function to unite two tables. Not sure about, whether this can be done using INNER JOIN. This solution is something, which quickly came to my mind.

On a higher level, what I have did here is stored the data from two tables into an array and updated the second table with the data stored in the array.

Table TableA = Table("TableA");
Table TableB = Table("TableB");
int ArraySize = TableA.numRows + TableB.numRows;
Array TableDataRows = Array(ArraySize);
Array TableDataCols = Array(TableA.numCols);
Table resultA = Table.query("SELECT *FROM TableA");// Selecting From TableA
int NrofTableARows = TableA.numRows;
int NrofTableACols = TableA.numCols;
for(int i=1;i<=NrofTableARows;i++) // Storing Result in the Array
{
	for(int k=1;k<=NrofTableACols;k++)
	{
	 TableDataCols[k]=	resultA[i][k];
	}
	TableDataRows[i] = TableDataCols.clone();
}
Table resultB = Table.query("SELECT *FROM TableB");// Selecting From TableB
int NrofTableBRows = TableB.numRows;
int NrofTableBCols = TableB.numCols;
for(int i=1;i<=NrofTableBRows;i++)// Storing Result in the Array
{
	for(int k=1;k<=NrofTableBCols;k++)
	{
	 TableDataCols[k]=	resultB[i][k];
	}
	TableDataRows[i+ NrofTableBRows] = TableDataCols.clone();
}
TableB.setSize(ArraySize,3);//Setting the table size.
for(int i=1;i<=ArraySize;i++) // Writing Data to the Table from Array
{
	for(int k=1;k<=NrofTableBCols;k++)
	{
		TableB[i][k]= TableDataRows[i][k];
	}
}

See the attached model.

Regards,

Arun KR

· 1

Enrique Elizaga commented · Jan 18, 2018 at 12:58 AM

Wow @Arun KR that seems complicated. Wouldnt it be easier:

for(int ROW=1; ROW <= Table("Origin").numRows; ROW++)
{
	Table("Destination").addRow();
	for(int COL=1; COL<= Table(x).numCols; COL++)
	{
		Table("Destination")[Table("Destination").numCols][COL] =
			Table("Origin")[ROW][COL];
	}	
}

0 ·

Answer 2 · 2020-04-02T10:22:20Z

martin.j answered Apr 2, '20

Update for future reference: Apparently the UNION ALL SQL feature is now available in FlexSim SQL interface, so this task is possible using one of the new calculated tables. It must have been added in Flexsim 19.01 or 20.01

question