[2005] Merging 5 datatables using first Column as key

[dinosaur_uk]

Hi all.

This has got me stuck for days and days now!

I've got a whole lot of datatables and the first column is the same for all 5 database (ie date)

How do i merge all of them into one table? Using the date column as the common ID.

Cheers

Ken

[stanav]

Have you tried using the DataTable.Merge method?

[Tom Sawyer]

Are you saying that you have five different tables and you want to display them as one table based on a common key?

ie - Table1 has Date, Field1, Field2; Table2 has Date, Field3, Field4 and you want to display one record:

Date, Field1, Field2, Field3, Field4

If that's the case, you're probably a lot better off joining them at the database level and only returning a single table to manipulate in the DataTable object.

[dinosaur_uk]

how do i do that at the database level?

[Tom Sawyer]

Right now, I assume you have 5 select statements such as:

SELECT Date, Field1a, Field1b FROM Table1
SELECT Date, Field2a, Field2b FROM Table2
SELECT Date, Field3a, Field3b FROM Table3
SELECT Date, Field4a, Field4b FROM Table4
SELECT Date, Field5a, Field5b FROM Table5

To select them all into the same row, you need to join each table based on the common field in them (Date) and select the fields you want from each table:

SELECT Table1.Date, Table1.Field1a, Table1.Field1b,
    Table2.Field2a, Table2.Field2b,
    Table3.Field3a, Table3.Field3b,
    Table4.Field4a, Table4.Field4b,
    Table5.Field5a, Table5.Field5b

FROM Table1
    JOIN Table2 ON Table2.Date = Table1.Date
    JOIN Table3 ON Table3.Date = Table1.Date
    JOIN Table4 ON Table4.Date = Table1.Date
    JOIN Table5 ON Table5.Date = Table1.Date

That way, each date will then be displayed in a single row with all the data from each table with it.

[dinosaur_uk]

What if one of my datatable is from another source?

[wild_bill]

If you can link the servers, Tom's suggestion would be best. If you can't something like this should work.

Dim dsAll As New DataSet
dsAll.Tables.AddRange(New DataTable() {dt1, dt2, dt3, dt4, dt5})

For Each dt As DataTable In dsAll.Tables
    dt.PrimaryKey = New DataColumn() {dt.Columns(0)}
Next

'use first table to store all data
For Each dr1 As DataRow In dsAll.Tables(0).Rows
    For iTable As Integer = 1 To dsAll.Tables.Count - 1
        'add columns from this table to the first one
        For iCol As Integer = 1 To dsAll.Tables(iTable).Columns.Count - 1
            dsAll.Tables(0).Columns.Add(iTable.ToString & icol.ToString)
        Next
        Dim dr As DataRow = dsAll.Tables(iTable).Rows.Find(dr1(0))
        If Not dr Is Nothing Then
            For iCol As Integer = 1 To dsAll.Tables(iTable).Columns.Count - 1
                dr1.Item(iTable.ToString & icol.ToString) = dr.Item(iCol)
            Next
        End If
    Next
Next