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

Question

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

Have a look at [`SELECT DISTINCT`](https://www.sqlservertutorial.net/sql-server-basics/sql-server-select-distinct/). — Hel O'Ween, Apr 14 '22 at 07:05
@CaiusJard , If I do an event on the button a second time then the record data becomes duplicate in the example2 database — roy, Apr 14 '22 at 07:09
The `INSERT` statement is going to insert whatever the `SELECT` statement retrieves. How exactly would you identify a duplicate? Use that in the `WHERE` clause to exclude existing records. — user18387401, Apr 14 '22 at 09:21
By the way, this is a SQL question. It has nothing to do with VB.NET or OLE DB. The SQL code does not depend on either of those technologies. — user18387401, Apr 14 '22 at 09:22
@CaiusJard , you can see the second screenshot for datagridview i.e. database example2 becomes duplicate after I do sql insert into event for the second time — roy, Apr 14 '22 at 09:23
You wouldn't succeed in inserting duplicate records if the table had a primary key — Caius Jard, Apr 14 '22 at 14:03
@CaiusJard , I use the DBF database which doesn't have a primary key but I can think of the code column as the primary key. In the screenshot, the invoice detail table is based on the item, so I have 2 sales invoice tables, namely sales invoices and sales invoice details — roy, Apr 14 '22 at 17:30
@user18387401 , you mean from above sql add where with code column — roy, Apr 14 '22 at 17:34
https://learn.microsoft.com/en-us/previous-versions/troubleshoot/visualstudio/foxpro/create-primary-index ? — Caius Jard, Apr 14 '22 at 19:19

score 0 · Answer 1 · answered Apr 20 '22 at 06:34

0

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
)

answered Apr 20 '22 at 06:34

Catherine

163
6

thanks for the reply from you. answer code from you and then I do the second time "insert into" then the results in the database example2 become duplicates – roy Apr 20 '22 at 08:24
Have a look at what is actually in each of the EXAMPLE1 and EXAMPLE2 tables in the database using a query window, before and after the button click. Also, for the second time, instead of using Button1_Click to test, you could try running the above statement without the INSERT INTO EXAMPLE2, directly in a query window in the database. This will eliminate any possible influence of what's in session and the datagrid views. After the insert, the grids must reload from the database. – Catherine Apr 21 '22 at 02:07
,you mean I have to query directly in the database without "insert into" – roy Apr 21 '22 at 03:06
Yes. Just as a test to check that the select part does not return any records after you have inserted once. The select should only return new records that you have added to EXAMPLE1 and not yet inserted to EXAMPLE2. – Catherine Apr 21 '22 at 06:18

score 0 · Accepted Answer · answered Apr 21 '22 at 08:45

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

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

2 Answers2