Looped SQL query with excel column values

Question

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?

Do you only need to do this once? if so use excel formula to build the query — Mike Miller
– Mike Miller, Commented Oct 1, 2018 at 13:36
@MikeMiller I do only need to do this once. We're just moving from our old way of tracking these values to a SQL based solution so once they are in they are in for good. — Jb83
– Jb83, Commented Oct 1, 2018 at 14:14
@Dy.Lee I checked out the link you referred to earlier. That looks like it's exactly what I was looking for. Glad to know someone had the same issue before. — Jb83
– Jb83, Commented Oct 1, 2018 at 14:37

Mike Miller · Accepted Answer · 2018-10-01 13:38:32Z

1

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.

answered Oct 1, 2018 at 13:38

Mike Miller

16.6k1 gold badge22 silver badges27 bronze badges

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

1 Comment

Jb83 Over a year ago

So essentially I could just copy and paste all 500 individual queries into SSMS and it wold run them sequentially? This may be the simplest solution.

Collectives™ on Stack Overflow

Looped SQL query with excel column values

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related