C# Merge Two DataTables with same amount of columns but different names

Question

I have this C# Script that I am using in the "Run .NET script" action in Power Automate Desktop (PAD)

DT1.Merge(DT2);
FinalTable = DT1.DefaultView.ToTable();

These are the script parameters:

.NET parameter name	Type	Direction	Iput value	Output variable
DT1	Datatable	In	%DataTable%
DT2	Datatable	In	%DataTable2%
FinalTable	Datatable	Out		%TemporalTable%

But, I am having an issue. Both tables have the same amount of columns, but the column names are not the same. So, the tables look like this:

DT1:

Column1	Column2
214	324233
233	33
3	3

DT2:

Column3	Column4
3423	32454
55	5334

And I get this:

Column1	Column2	Column3	Column4
214	32433
233	33
3	3
		3423	32454
		55	5334

But, what I want for the result is this:

Column1	Column2
214	32433
233	33
3	3
3423	32454
55	5334

If I change the script to:

DT1.Merge(DT2, false, MissingSchemaAction.Ignore);
FinalTable = DT1.DefaultView.ToTable();

This is what I get:

Column1	Column2
214	32433
233	33
3	3

So I am not getting the values from DT2.

I want to do this and avoid using a for each loop.

So, Can it be done?

Thanks in advance!

Answer 1

But I am curious to see if there is a way to accomplish this

If you take a look at line 203 of RecordManager in the .NET source code, you'll see that naming the columns the same (as per the comment) is the way to do this if you wish to use the Merge method, which calls upon RecordManager to copy the data:

200    int count = table.Columns.Count;
201    for (int i = 0; i < count; ++i) {
202      DataColumn dstColumn = table.Columns[i];
203      DataColumn srcColumn = src.Columns[dstColumn.ColumnName];
204      if (null != srcColumn) {
205        object value = srcColumn[record];
206        ICloneable cloneableObject = value as ICloneable;

If you seek an alternative that doesn't use Merge(), you'll need to roll your own implementation

• Set one of the tables to have a primary key column, let's call it A
• Loop over the rows of the other table (let's call it B), looking its primary key values up using A.Rows.Find
• If you didn't find a row, make a new row
• Run a for loop that copies the data positionally to the found row/new row

Essentially this would be a reinvention of the wheel Merge already was; Merge is a very involved and considered block of code that copies everything from old and new (datarows track the original and modified values per column, state and errors)

In summary, I'd say that the best way to perform a positional copy is to just use a for loop to equate the column names before using Merge, so that RecordManager will end up doing a positional copy. It doesn't matter which table you pick on to alter the names of, so long as they're the same, but if one table has the names you want to use in the end, change the names in the other table