Answer to Question #180310 in Visual Basic for Montra

Question

I have two Tables Table1 and Table2 having fields CustID , Product, Price, VAddress in both tables. How to insert records of table2 into table1 if CustIDs are not Equal (Using VB)

Answer 1

SQL Script:

CREATE TABLE [dbo].[Table1]
(
	[CustID] INT NOT NULL PRIMARY KEY, 
    [Product] NVARCHAR(50) NULL, 
    [Price] FLOAT NULL, 
    [VAddress] NVARCHAR(50) NULL
)
INSERT INTO [dbo].[Table1] ([CustID], [Product], [Price], [VAddress]) VALUES (1, N'Product 465465', 566, N'VAdderss')
INSERT INTO [dbo].[Table1] ([CustID], [Product], [Price], [VAddress]) VALUES (2, N'Product 46', 23, N'VAdderss')




CREATE TABLE [dbo].[Table2]
(
	[CustID] INT NOT NULL PRIMARY KEY, 
    [Product] NVARCHAR(50) NULL, 
    [Price] FLOAT NULL, 
    [VAddress] NVARCHAR(50) NULL
)




INSERT INTO [dbo].[Table2] ([CustID], [Product], [Price], [VAddress]) VALUES (1, N'Test 1', 23, N'Test 1')
INSERT INTO [dbo].[Table2] ([CustID], [Product], [Price], [VAddress]) VALUES (2, N'Test 2', 458, N'Test 2')
INSERT INTO [dbo].[Table2] ([CustID], [Product], [Price], [VAddress]) VALUES (3, N'Test 3', 256, N'Test 3')
INSERT INTO [dbo].[Table2] ([CustID], [Product], [Price], [VAddress]) VALUES (4, N'Test 4', 757, N'Test 4')

Vb.net code:

Imports System.Data.SqlClient


Module Module1
    Private databaseConnectionString As String = "Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\CustomersDataBase.mdf;Integrated Security=True;Connect Timeout=30"
    Sub Main()
        Try
            'Read table2
            Dim tblTable2 As New DataTable("tblTable2")
            Using connection As New SqlConnection(databaseConnectionString)
                Using adapter As New SqlDataAdapter("SELECT * from Table2", connection)
                    adapter.Fill(tblTable2)
                End Using
            End Using
            'insert records of table2 into table1 if CustIDs are not Equal
            For i As Integer = 0 To tblTable2.Rows.Count - 1
                'Read table1
                Dim tblTable1 As New DataTable("tblTable1")
                Using connection As New SqlConnection(databaseConnectionString)
                    Using adapter As New SqlDataAdapter("SELECT * from Table1 where CustID = '" + tblTable2.Rows(i)(0).ToString() + "'", connection)
                        adapter.Fill(tblTable1)
                    End Using
                End Using


                If (tblTable1.Rows.Count = 0) Then
                    Using connection As New SqlConnection(databaseConnectionString)


                        Dim mycommand = New SqlCommand("insert into Table1([CustID],[Product],[Price],[VAddress]) values (" &
                                                      Integer.Parse(tblTable2.Rows(i)(0)) & ",'" & tblTable2.Rows(i)(1) & "'," &
                                                       Double.Parse(tblTable2.Rows(i)(2)) &
                                                      ",'" & tblTable2.Rows(i)(3).ToString() & "')", connection)
                        connection.Open()


                        mycommand.ExecuteNonQuery()


                    End Using
                End If
            Next


            'Read table1
            Dim tblTable1Read As New DataTable("tblTable11")
            Using connection As New SqlConnection(databaseConnectionString)
                Using adapter As New SqlDataAdapter("SELECT * from Table1", connection)
                    adapter.Fill(tblTable1Read)
                End Using
            End Using
            For i As Integer = 0 To tblTable2.Rows.Count - 1
                Console.WriteLine(tblTable1Read.Rows(i)(0).ToString() + "  " + tblTable1Read.Rows(i)(1).ToString() + "  " + tblTable1Read.Rows(i)(2).ToString() + "  " + tblTable1Read.Rows(i)(3).ToString())
            Next
        Catch ex As Exception
            Console.WriteLine(ex.ToString())
        End Try


        Console.ReadLine()
    End Sub


End Module