Okay, as you can probably tell from the last part of the title, this is a non-ideal situation.
I'm using a piece of industry-specific web-interface software that I guarantee no one has ever heard of, and while the functionality it offers to do mass data entry is meager, it does allow me to run SQL queries directly on its underlying MS SQL database through an 8,000 character-limited web form. That's the most direct access I have to the database.
I need to update a particular field in about 3,700 records with unique data for each. As per this answer, I've been doing the following:
UPDATE guest SET info4 = CASE guestid WHEN 12345 THEN 'BNID: 917546553' WHEN 67890 THEN 'BNID: B04695465' [etc, etc.] ELSE info4 END
info4 field is currently empty for all records.) I can fit about 200 cases into one query before hitting the character limit and having to run a new update with the next chunk of data.
Things I have discovered so far:
- Not including the
ELSEstatement at the end will empty the field on all records that don't fulfill a case.
- Before committing, I get "Rows affected: [# of rows in the table]" instead of the number of rows that will actually get new data.
I understand I should be putting a
WHERE guestid IN (12345, 67890) at the end to avoid running the update on every row, but that's going to be hellish to avoid running over my character limit with each chunk of update statement.
As it happens, I am currently taking my source data from an Excel spreadsheet, pasting it all into Notepad++, running find/replaces on it to insert the necessary SQL syntax, and then chunking it up as per the character limit.
So, in a nutshell, my question is: have I explored this to the extent of what is possible already? Is there something big and obvious I'm missing, or should I be going about this in a completely different way? Bonus points for fewer characters per case!