如何使用excel表中的新数据更新sql server表?

I have managed to get data from an excel sheet into my SQL SERVER table through VB.net but every time I run the query (click the button), the same data is uploaded again to my table. Is there a way to import only the additional data ? (column 'Email' should be unique in my SQLSERVER table) Below is my code:

我已经通过VB.net成功地从excel表中获取了数据，但是每次运行查询(单击按钮)，相同的数据就会再次上载到我的表中。是否有只导入附加数据的方法?(列“Email”在我的SQLSERVER表中应该是唯一的)下面是我的代码:

Thank you for your help !

谢谢你的帮助!

 Dim ExcelConnection As New System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\Excel.xlsm;Extended Properties=""Excel 12.0 Macro;HDR=No""")
        ExcelConnection.Open()

        Dim expr As String = "SELECT * FROM [Sheet1$]"

        Dim objCmdSelect As OleDb.OleDbCommand = New OleDb.OleDbCommand(expr, ExcelConnection)
        Dim objDR As OleDb.OleDbDataReader

        Dim SQLconn As New SqlConnection()
        Dim ConnString As String = "Data Source=USER\SQLEXPRESS;Initial Catalog=DBName;Integrated Security=SSPI"
        SQLconn.ConnectionString = ConnString
        SQLconn.Open()


        Using bulkCopy As SqlBulkCopy = New SqlBulkCopy(SQLconn)
            bulkCopy.DestinationTableName = "TableName"

            Try
                objDR = objCmdSelect.ExecuteReader
                bulkCopy.WriteToServer(objDR)
                objDR.Close()
                SQLconn.Close()

            Catch ex As Exception
                MsgBox(ex.ToString)
            End Try
        End Using

2 个解决方案

#1

If your EMail field is just an Unique Index then you could change the property

如果您的电子邮件字段只是一个唯一的索引，那么您可以更改属性

`Ignore Duplicate Key`

and set its value to Yes on the property page for the Index definition (Sql Management Studio).
In this way SqlBulkCopy will try to add, but in case of duplicates the record will be discarded without problems. This property settings is equivalen to the INDEX_OPTION IGNORE_DUP_KEY = ON

并在索引定义(Sql Management Studio)的属性页中将其值设置为Yes。这样，SqlBulkCopy将尝试添加，但是如果重复，记录将毫无问题地被丢弃。此属性设置与INDEX_OPTION IGNORE_DUP_KEY = ON相等。

Another option, with less side effects, but a bit more complex, is to add your values in a temporary table and prepare a stored procedure that copies the records from the temporary table into the final one checking the insert for duplicates. (I haven't tried this)

另一种选择是在临时表中添加值，并准备一个存储过程，将临时表中的记录复制到最后一个表中，检查是否插入重复的记录。(我没有试过这个)

#2

Can you just add another column to your excel named something like UploadDate so after each import you just update this column in excel and you’re good.

你能不能在你的excel中添加另一个列，比如UploadDate，在每次导入之后，你只要用excel更新这个列就行了。

You’ll have to update your SELECT query to something like “SELECT * FROM [Sheet1$] WHERE UploadDate is null”

必须将SELECT查询更新为“SELECT * FROM [Sheet1$]，其中UploadDate为空”

If you don’t have a ton of data then you might not need to use bulk import. In that case you can easily generate insert statements that look like this.

如果您没有大量的数据，那么您可能不需要使用批量导入。在这种情况下，您可以轻松生成如下所示的insert语句。

IF NOT EXISTS (SELECT * from tableName where email = @email)
    INSERT (column1, column2,...) values (val1, val2,...)

And execute bunch of these at once.

同时执行这些。

#1