Transfering Data with VBA(Excel) to SQL Server but avoiding duplicate column?

Question

My main objective is to transfer some data from excel to SQL server while using vba but in the process I would like to detect and avoid transferring some duplicate month columns.

This is what i have so far:

Sub UploadExcelToSQL() Dim adoCN As ADODB.Connection Dim sConnString As String Dim sSQL As String Dim lRow As Long, lCol As Long sConnString = "Provider=SQLOLEDB;Data Source=agrsql004\instance01;Initial Catalog=DEHL;Integrated Security=SSPI" Set adoCN = CreateObject("ADODB.Connection") adoCN.Open sConnString For lRow = 2 To 4 sSQL = "INSERT INTO dbo.MonthlyValue (ID, Year, Month, Value) " & _ " VALUES (" & _ "'" & Sheet2.Cells(lRow, 1) & "', " & _ "'" & Sheet2.Cells(lRow, 2) & "', " & _ "'" & Sheet2.Cells(lRow, 3) & "', " & _ "'" & Sheet2.Cells(lRow, 4) & "')" adoCN.Execute sSQL Next lRow adoCN.Close Set adoCN = Nothing End Sub

And will return something like:

ID Year Month Value 123 2018 1 9987 123 2018 2 80988 123 2018 1 8990

What I would like is to have something like this:

ID Year Month Value 123 2018 1 9987 123 2018 2 80988

In conclusion, I would like the code to detect the duplicate months of an equal year and avoid transferring it to the DB in SQL server so that one ID can only have one value per month of x year. My knowledge is pretty limited in this area still so any help would be greatly appreciated.

all you need is a condition.. If..Then..Else. End If. Also try avoid to use query as string.. add parameters to your command.. You have so many ways to do this.. preprocess excel file.. or before insert, ask db, if the record exists — Peter Ksenak
– Peter Ksenak, Commented Jun 14, 2019 at 11:45
A unique constraint would work but if i wanted to add new rows for same id, same month but with different years how could i do that? — Johnny Abreu
– Johnny Abreu, Commented Jun 14, 2019 at 13:40

FunThomas · Accepted Answer · 2019-06-14 15:59:53Z

One solution would be to create a Unique constraint to the table on the cobination of the three columns ID, Year and Month:

Alter Table dbo.MonthlyValue Add Constraint MonthlyValueUniqueContraint UNIQUE (ID, Year, Month)

Note that you cannot create the constraint if you already have duplicates in your database - in that case you first have to cleanup your data.

This means that you can have multiple records with the same ID, the same year or the same month, you can even have multiple records for the same ID and same year (but different month) or same ID and month (but different year). Just the combination of ID, year and month would be unique.

When you try to insert a combination that already exists (eg like your 3rd row), the adoCN.Execute will throw an error - you have to catch this in your program so that it continues. Be carefull, the following code would ignore any error, if you want to have a robust solution, you should check the error code and only continue if it was a Unique-Contraint error (and not, for example an error because the connection dropped or one of the thousand other errors that may happen).

On Error Resume Next adoCN.Execute sSQL If Err.Number <> 0 Then Debug.Print Err.Number, Err.Description On Error GoTo 0

Of course, an alternative could always be to check if the record already exists in the database.
Disadvantage is that you need 2 round-trips to the database for every row, advantage is that you can implement an Update-statement if you want to keep the second rather than the first value. Or you could write a small stored procedure so that the logic is executed on database side - but I think that leads too far for now.

In any case, you should set the constraint - let the database watch that your data stays clean.

I tried the first option that you suggested Thomas and it worked perfectly. The second option would also work, but for what I need right now the first one is the ideal solution. Thanks for the help.

Collectives™ on Stack Overflow

Transfering Data with VBA(Excel) to SQL Server but avoiding duplicate column?

1 Answer 1

1 Comment

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Related