sql查询（插入）在OLEDB中，无重复的vb.net中

Question

我希望没有重复的sql query命令（插入到OLEDB中），并且有最好的建议吗？

谢谢 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

Answer 1

要仅插入示例2中尚不存在的示例1的行，您将需要比较一行中每列的值，而每行不希望重复。

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
)

Answer 2

我根据下面的链接获得答案解决方案
（避免在SQL Server中插入SQL Server中的select Query中的重复项/a>）

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