Joining DataTables

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there anyway of Joining two or more DataTable with similar structure?

I have three DataTables with following structures

Data, AmountB/F, Repayments, InterestCharged and AmountC/F

i want to join these tables on the basis of Date. I don't want three rows for each date, i want the values to be added for similar dates.

thanx
 
Depending on the size of the tables, this operation (which is not a join...
it's a merge with roll-up), can take an extraordinarily long period of time.

Regardless, take a look at the syntax for the UNION keyword in the SELECT
statement.
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_sa-ses_9sfo.asp

Otherwise knows as a UNION Query, you can combine the results from a number
of queries and then group by specific columns.

Normally, this kind of functionality is performed in an ETL operation on a
periodic basis (often daily) into a data cube and simply queried from there
using MDX. If you are not familiar with SQL Analysis Server, you may want
to take a look.

--- Nick

Job Lot said:
Is there anyway of Joining two or more DataTable with similar structure?

I have three DataTables with following structures

Data, AmountB/F, Repayments, InterestCharged and AmountC/F

i want to join these tables on the basis of Date. I don't want three rows
Click to expand...
for each date, i want the values to be added for similar dates.
 
Job Lot,
It sounds like you want to update one DataTable with the values of a second
DataTable, rather then doing a database "Join" or a database "Union".

You will need to manually do this, with a loop similar to:

Public Shared Sub Main()
Dim ds As New DataSet("Job Lot")
Dim table1 As DataTable = GetTable("table1")
Dim table2 As DataTable = GetTable("table2")
Dim table3 As DataTable = GetTable("table3")
ds.Tables.Add(table1)
ds.Tables.Add(table2)
ds.Tables.Add(table3)
AddTable(table1, table2)
AddTable(table1, table3)
End Sub

Private Shared Sub AddTable(ByVal mainTable As DataTable, ByVal
sourceTable As DataTable)
For Each sourceRow As DataRow In sourceTable.Rows
If mainTable.Rows.Contains(sourceRow("Data")) Then
Dim mainRow As DataRow = mainTable.Rows.Find(sourceRow!Data)
mainRow.BeginEdit()
mainRow("AmountB/F") = CDec(mainRow("AmountB/F")) +
CDec(sourceRow("AmountB/F"))
mainRow("Repayments") = CDec(mainRow("Repayments")) +
CDec(sourceRow("Repayments"))
mainRow("InterestCharged") =
CDec(mainRow("InterestCharged")) + CDec(sourceRow("InterestCharged"))
mainRow("AmountC/F") = CDec(mainRow("AmountC/F")) +
CDec(sourceRow("AmountC/F"))
mainRow.EndEdit()
Else
mainTable.ImportRow(sourceRow)
End If
Next
End Sub

Private Shared Function GetTable(ByVal tableName As String) As DataTable
Dim table As New DataTable(tableName)
With table.Columns
.Add("Data", GetType(DateTime))
.Add("AmountB/F", GetType(Decimal))
.Add("Repayments", GetType(Decimal))
.Add("InterestCharged", GetType(Decimal))
.Add("AmountC/F", GetType(Decimal))
End With
table.PrimaryKey = New DataColumn() {table.Columns("Data")}
With table.Rows
.Add(New Object() {#1/1/2004#, 10, 20, 30, 40})
.Add(New Object() {#4/1/2004#, 10, 20, 30, 40})
.Add(New Object() {#7/1/2004#, 10, 20, 30, 40})
End With
Return table
End Function

Hope this helps
Jay

Job Lot said:
Is there anyway of Joining two or more DataTable with similar structure?

I have three DataTables with following structures

Data, AmountB/F, Repayments, InterestCharged and AmountC/F

i want to join these tables on the basis of Date. I don't want three rows
Click to expand...
for each date, i want the values to be added for similar dates.
 
Hi Jay,

thanks for the code. i am getting an error saying "Table does not have a primary key", whereas i have created pk column in my datatable??? :(
 
Back
Top