Way to update many (>3000) records with unique data, using a character limited web form

Question

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

(The 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 ELSE statement 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!

Answer 1

The SET can be made a bit simpler:

SET info4 = 'BNID: ' +
  CASE guestid
    WHEN 12345 THEN '917546553'
    WHEN 67890 THEN 'B04695465'
      [etc, etc.]
                          --- no ELSE here
  END 

WHERE info4 IS NULL       --- and this is needed

Saves 6 characters per case.

---

If you can create a table (even a temporary one) you could also use:

--- run once
CREATE TABLE #tmp
( guestid INT NOT NULL
, i4 VARCHAR(200)
, PRIMARY KEY (guestid)
) ;

--- as many times as needed
INSERT INTO #tmp VALUES
(12345,'917546553'),
...
(67890,'B04695465');

and then:

UPDATE guest
  SET info4 = 'BNID: ' + i4
FROM guest
  JOIN #tmp
    ON #tmp.guestid = guest.guestid
WHERE guest.info4 IS NULL

Answer 2

Try something like this:

WHILE 1=1
BEGIN
    UPDATE TOP (10000) guest
    SET info4 = CASE guestid
    WHEN 12345 THEN 'BNID: 917546553'
    WHEN 67890 THEN 'BNID: B04695465'
    [etc, etc.]
    ELSE info4
    END
    WHERE info4 IS NULL
    IF @@ROWCOUNT<10000 BREAK;
END

This will update 10k rows at a time. Since the only rows you need to update will have a NULL for info4, we filter for that.