Insert new records only into SQL Table Using VBA
Your SQL query isn't quite right - there is no THEN
in a SQL IF
.
Also, you don't need to do anything if it does exist, so just use if not exists.
"IF NOT EXISTS (SELECT 1 FROM dbo.Customers WHERE FirstName = '" & sFirstName & "' and LastName = '" & sLastName & "') " & _
"INSERT INTO dbo.Customers (FirstName, LastName) " & _
"VALUES ('" & sFirstName & "', '" & sLastName & "')"
Jamsandwich
Updated on July 17, 2022Comments
-
Jamsandwich almost 2 years
I have an Excel workbook with the below code -
Sub Button1_Click() Dim conn As New ADODB.Connection Dim iRowNo As Integer Dim sFirstName, sLastName As String With Sheets("Sheet1") 'Open a connection to SQL Server conn.Open "Provider=SQLOLEDB;" & _ "Data Source=server1;" & _ "Initial Catalog=table1;" & _ "User ID=user1; Password=pass1" 'Skip the header row iRowNo = 2 'Loop until empty cell in CustomerId Do Until .Cells(iRowNo, 1) = "" sFirstName = .Cells(iRowNo, 1) sLastName = .Cells(iRowNo, 2) 'Generate and execute sql statement ' to import the excel rows to SQL Server table conn.Execute "Insert into dbo.Customers (FirstName, LastName) " & _ "values ('" & sFirstName & "', '" & sLastName & "')" iRowNo = iRowNo + 1 Loop MsgBox "Customers imported." conn.Close Set conn = Nothing End With End Sub
This opens up a connection to my database and inputs the values from the stated columns.
The primary key is an incremental key on the database. The problem is it will copy ALL values.
I'd like to add new rows of data into the Excel Sheet and only insert those rows that don't already exist.
I've tried different methods ('merge', 'if exist', if not exist', etc.) but I can't get it right.
The solution has to be through VBA. Setting up a link using SSMS is not an option.
I understand that it may be possible to use temporary tables and then trigger a procedure which performs the merge but I want to look into that as a last resort. Haven't read up on it yet (making my way through my MS SQL bible book) but I'm hoping it won't be necessary.
---Update from @Kannan's answer---
New portion of VBA -
conn.Execute "IF EXISTS (SELECT 1 FROM dbo.Customers WHERE FirstName = '" & sFirstName & "' and LastName = '" & sLastName & "') " & _ "THEN UPDATE dbo.customers SET WHERE Firstname = '" & sFirstName & "' and LastName = '" & sLastName & "' " & _ "ELSE INSERT INTO dbo.Customers (FirstName, LastName) " & _ "VALUES ('" & sFirstName & "', '" & sLastName & "')"
This returns error 'Incorrect syntax near the keyword 'THEN'.
-
Jamsandwich over 7 yearshi @Kannan, I've added this into my code and updated my question. Do you know why I may be getting the error that I mentioned. It looks like you missed a closing bracket in your first line. I think I added it in the right place.
-
Jamsandwich over 7 yearsAlso, I wasn't too sure how to input the set command. I assume "--other columns" was the commented section? So I took that out.
-
Jamsandwich over 7 yearsThat's spot on. Thankyou BeanFrog. I had actually tried IF NOT EXISTS before, but I obviously didn't get it right. I certainly didn't use the 'Select 1' part. Could you tell me how the SELECT 1 works?
-
BeanFrog over 7 yearsIt's just a regular select statement, but instead of any columns, you select the number 1. All this does is to prevent the overhead of getting all the column details, when all you need to know is 'does this row exist' rather than 'what is in this row'.
-
Jamsandwich over 7 yearsAwesome. Thanks again. This is going to set me up nicely once finally start working on SQL exclusively.