Reputation: 1845
I have an excel sheet with some data:
1. Inventory item number
2. Description
3. Inventory Database ID (PRIMARY KEY)
I have about 1000 rows. I want to delete the item numbers in the database that match the item number in my excel list. I can write an application to do that in .NET, but that just seems overly complicated.
Is there an easy way through excel or SQL Server to run a sql statement to delete item numbers in my excel sheet with out the trouble of creating an application?
Upvotes: 1
Views: 9306
Reputation: 5962
If you do not want to go through a SQL interface, you can run the attached code from excel, after updating the connection string obviously.
Sub ExecuteSQLCommand()
'Execute the SQL string passed through
Dim conn As ADODB.Connection, strConn As String, sSQLCommand As String
Dim cmd As ADODB.Command, lLoop As Long, lLastRow As Long
Set conn = New ADODB.Connection
conn.ConnectionString = "Provider=SQLOLEDB;" & _
"Data Source=DCC08SQL;" & _
"Initial Catalog=HarvestPress;" & _
"Integrated Security=SSPI;" & _
"Database=HarvestPress;" & _
"Trusted_Connection=True;"
conn.Open
Set cmd = New ADODB.Command
lLastRow = Cells(Rows.Count, 1).End(xlUp).Row
With cmd
.ActiveConnection = conn
.CommandType = adCmdText
For lLoop = 1 To lLastRow
sSQLCommand = "DELETE FROM Table1 WHERE ItemNumber='" & Cells(lLoop, 1) & "' AND InventoryId = " & Cells(lLoop, 1)
.CommandText = sSQLCommand
.Execute
Next lLoop
End With
conn.Close
Set conn = Nothing
Set cmd = Nothing
End Sub
Upvotes: 0
Reputation: 12003
For quick updates. I find this to be the best method.
Add a column to Excel and construct your update statement as a formula, ie:
="DELETE Table1 WHERE ItemNumber='"&A1&"' AND InventoryId = "&C1
Copy the formula down, and copy/paste the result into an SQL window and run it.
Pro tip, if you have a lot of apostrophes to deal with, it might be worth it to do a global find/replace beforehand. Or you can deal with them from the formula. ie:
="DELETE Table1 WHERE ItemNumber='"&SUBSTITUTE(A1,"'","''")&"' AND InventoryId = "&C1
Upvotes: 3