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=agrsql004instance01;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.
Advertisement
Answer
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.