Joining DataTables

[Job Lot]

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

[Nick Malik]

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/de...a-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" <Jo****@discussions.microsoft.com> wrote in message
news:2D**********************************@microsof t.com...

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

[Jay B. Harlow [MVP - Outlook]]

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" <Jo****@discussions.microsoft.com> wrote in message
news:2D**********************************@microsof t.com...

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

[Job Lot]

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??? :(

"Jay B. Harlow [MVP - Outlook]" wrote:

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" <Jo****@discussions.microsoft.com> wrote in message
news:2D**********************************@microsof t.com...

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

[Job Lot]

I found the error in my code. Thanx a ton for your help

"Jay B. Harlow [MVP - Outlook]" wrote:

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" <Jo****@discussions.microsoft.com> wrote in message
news:2D**********************************@microsof t.com...

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