CODEWITHSUNDEEP
sql-serverexcel
Peter Sun
Peter Sun

Reputation: 1845

Updating SQL SERVER Database from EXCEL

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

Answers (2)

nutsch
nutsch

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

Malk
Malk

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

Related Questions