Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

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

(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!

share|improve this question
Do all the strings you put there start with 'BNID: ' ? – ypercubeᵀᴹ May 17 '12 at 18:45
Yes--I'd love to extend the database with a new field, but can't for various reasons. That's there because the field could be used for other things, and is displayed in an agnostic context. – NReilingh May 17 '12 at 18:53
up vote 3 down vote accepted

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

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
( guestid INT NOT NULL
, i4 VARCHAR(200)
, PRIMARY KEY (guestid)
) ;

--- as many times as needed

and then:

UPDATE guest
  SET info4 = 'BNID: ' + i4
FROM guest
  JOIN #tmp
    ON #tmp.guestid = guest.guestid
WHERE guest.info4 IS NULL
share|improve this answer
Nice! Fist example is exactly what I needed. – NReilingh May 17 '12 at 19:34

Try something like this:

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

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.

share|improve this answer
That WHERE statement should go after the first END, right? – NReilingh May 17 '12 at 19:03
@NReilingh yes, sorry – JNK May 17 '12 at 19:08
Hm, I'm getting "Cannot use a BREAK statement outside the scope of a WHILE statement." – NReilingh May 17 '12 at 19:08
@NReilingh try now, I left out the BEGIN – JNK May 17 '12 at 19:09

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.