SQL query (insert into) in oledb without duplicates in VB.NET-CodePudding

I want the SQL query command (insert into) in oledb without duplicates and is there the best recommendation?.

Thanks jack

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            connectionString = cn
            con = New OleDbConnection(connectionString)
            con.Open()
            Dim sql As String = "INSERT INTO EXAMPLE2 SELECT * FROM EXAMPLE1"
            cmd = New OleDbCommand(sql, con)
            cmd.ExecuteNonQuery()
            MessageBox.Show("Successfully Updated...", "Update")
            con.Close()
            Me.fillDataGridView1()
            Me.fillDataGridView2()
        Catch myerror As OleDbException
            MessageBox.Show("Error: " & myerror.Message)
        Finally
        End Try
    End Sub

CodePudding user response：

To only insert rows from EXAMPLE1 that do not already exist in EXAMPLE2, you will need to compare the value of each column in a row that you don't want duplicated.

INSERT INTO EXAMPLE2 SELECT * FROM EXAMPLE1 EX1
WHERE NOT EXISTS
(SELECT * FROM EXAMPLE2 EX2 WHERE 
EX2.CODE = EX1.CODE AND
EX2.NOD = EX1.NOD AND
EX2.QTY = EX1.QTY AND
EX2.PRICE = EX1.PRICE AND
EX2.REMARK = EX1.REMARK
)

CodePudding user response：

I get an answer solution based on the link below (Avoid duplicates in INSERT INTO SELECT query in SQL Server)

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Try
            connectionString = cn
            con = New OleDbConnection(connectionString)
            con.Open()
            Dim sql As String = "INSERT INTO EXAMPLE2 (CODE,NOD,QTY,PRICE) SELECT t1.CODE, t1.NOD,t1.QTY, t1.PRICE FROM EXAMPLE1 t1 WHERE NOT EXISTS(SELECT CODE FROM EXAMPLE2 t2 WHERE t2.CODE = t1.CODE)"
            cmd = New OleDbCommand(sql, con)
            cmd.ExecuteNonQuery()
            MessageBox.Show("Successfully Updated...", "Update")
            con.Close()
            Me.fillDataGridView1()
            Me.fillDataGridView2()
        Catch myerror As OleDbException
            MessageBox.Show("Error: " & myerror.Message)
        Finally
        End Try
    End Sub