What I am trying to do is create a statement that allows me to insert related sources to one table and use matching criteria to update the other. For instance,
Table 1 known as (personal) has ID, First_name, Last_name, Phone_number, Address, DOB
table 2 matching table has ID, First_name, Last_name, Phone_number, Address, DOB, Individual_ID, Source, Reference
Table 3 Known as (sources) has ID, Individual_ID(which relates to the ID column of the first table), Source, Reference
All I am trying to do is when table 1 and table 2 match update table 1 and table 3. What is the best way to use three tables in a match statement?
Here is what I have so far:
Try
Dim sqlcon As New SqlConnection("SQLCONSTRING")
Dim cb2 As New StringBuilder("MERGE INTO ")
cb2.AppendFormat("{0} as T using {1} as S", "[" + ListBox1.SelectedItem.ToString + "]", "[" + ListBox2.SelectedItem.ToString + "]")
Dim sbOn As New StringBuilder(" on ")
Dim andRequired As Boolean = False
For Each item In CheckedListBox1.CheckedItems
If andRequired Then
sbOn.Append(" AND ")
End If
Dim columnName As String = item.ToString()
sbOn.AppendFormat("T.[{0}] = S.[{1}]", columnName, columnName)
andRequired = True
Next
cb2.Append(sbOn.ToString())
Dim sbOn2 As New StringBuilder("")
Dim andRequired2 As Boolean = False
For Each item In CheckedListBox2.CheckedItems
If andRequired2 Then
sbOn2.Append(", ")
End If
Dim columnName As String = item.ToString()
sbOn2.AppendFormat("T.[{0}] = S.[{1}]", columnName, columnName)
andRequired2 = True
Next
cb2.AppendFormat(" When Matched then Update Set ")
cb2.Append(sbOn2.ToString() + ";")
Dim sbOn1 As New StringBuilder(" on ")
Dim andRequired1 As Boolean = False
For Each item In CheckedListBox1.CheckedItems
If andRequired1 Then
sbOn1.Append(" AND ")
End If
Dim columnName As String = item.ToString()
sbOn1.AppendFormat("T1.[{0}] = T2.[{1}]", columnName, columnName)
andRequired1 = True
Next
Dim dr As DialogResult = MessageBox.Show("Would you like to delete matched records from " + ListBox2.SelectedItem.ToString + "?",
"Remove duplicates", MessageBoxButtons.YesNo)
If dr = DialogResult.Yes Then
cb2.AppendFormat(" DELETE T1 FROM [{0}] T1 JOIN [{1}] T2", ListBox2.SelectedItem.ToString, ListBox1.SelectedItem.ToString)
cb2.Append(sbOn1.ToString() + ";")
Dim sql As String = cb2.ToString()
sqlcon.Open()
Dim cmd As SqlClient.SqlCommand
cmd = New SqlClient.SqlCommand(sql, sqlcon)
cmd.CommandTimeout = False
Dim myReader As SqlDataReader = cmd.ExecuteReader
myReader.Read()
Dim I As Integer = myReader.RecordsAffected
Dim S As String = I
MessageBox.Show(S.ToString + " records effected")
sqlcon.Close()
Else
Dim sql As String = cb2.ToString()
MessageBox.Show(cb2.ToString)
sqlcon.Open()
Dim cmd As SqlClient.SqlCommand
cmd = New SqlClient.SqlCommand(sql, sqlcon)
cmd.CommandTimeout = False
Dim myReader As SqlDataReader = cmd.ExecuteReader
myReader.Read()
Dim I As Integer = myReader.RecordsAffected
Dim S As String = I
MessageBox.Show(S.ToString + " records effected")
sqlcon.Close()
End If
Catch ex As Exception
End Try
What I have tried:
I have made the match statement using both table one and table 2 provided in the above analysis. However, I am unsure how to include the 3rd table in a match and merge statement.