CODEWITHSUNDEEP
excelvba
Jb83
Jb83

Reputation: 147

Looped SQL query with excel column values

Basically I need to run an update query on a table with about 500 entries in it. I need to update the column called pointvalue with different values based on the column agentname. So if I just needed to do it with one single value it would look like this. 

UPDATE attendance SET PointValue = '5' WHERE AgentName = 'Example Value'

The kicker is that I need to do this with 500 different values for 500 different people. Currently I have each person's name and point value in an excel spreadsheet. Column A contains names, columb B contains values. Is there a way to write a VBA script that can run through the spreadsheet and run an update query for each row? Something like 

UPDATE attendance SET PointValue = 'A2' WHERE AgentName = 'A1'

UPDATE attendance SET PointValue = 'B2' WHERE AgentName = 'B1'

and then on and on until it reaches the bottom?

Upvotes: 0

Views: 146

Answers (1)

Mike Miller
Mike Miller

Reputation: 16575

I'd use a formula in the 3rd column which effectively builds the queries and then cut and paste to run them.

="UPDATE attendance SET PointValue = '" & A2 & "' WHERE AgentName = '" & A1 & "'"

You might get some SQL escaping issues but won't take long to fix.

Upvotes: 1

Related Questions