Updating SQL SERVER Database from EXCEL

Question

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?

do you have access to SQL Server Management Studio?

nutsch
– nutsch

2013-12-11 00:46:45 +00:00
Commented Dec 11, 2013 at 0:46 — nutsch
– nutsch, Commented Dec 11, 2013 at 0:46

Malk · Accepted Answer · 2013-12-11 00:59:10Z

3

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

edited Dec 11, 2013 at 0:59

answered Dec 11, 2013 at 0:52

Malk

12k4 gold badges35 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

nutsch Over a year ago

for the apostrophes, you can do a quick update to your formula ="DELETE Table1 WHERE ItemNumber='"&substitute(A1,"'","")&"' AND InventoryId = "&C1

Malk Over a year ago

Thanks @nutsch, I updated my answer to be more thorough. Usually a global find/replace is safe as adding the column kind of destroys the file for other purposes anyway.

Peter Sun Over a year ago

I am not sure that is going to work for me. I have about 500 rows to delete. 10-20 rows maybe, any more than that it will be too much work..

andy holaday Over a year ago

Why is it too much work? The formula @Malk gave can simply be filled down, copied, pasted, and executed. Are there other requirements?

Peter Sun Over a year ago

could i generate a script and run through ssms? like 'code' DELETE FROM TABLE WHERE itemnumber=123 DELETE FROM TABLE WHERE itemnumber=124 DELETE FROM TABLE WHERE itemnumber=125 'code' and run each line?

|

nutsch · Accepted Answer · 2013-12-11 03:39:15Z

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

Collectives™ on Stack Overflow

Updating SQL SERVER Database from EXCEL

2 Answers 2

6 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

6 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related