Take the 2-minute tour ×
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. It's 100% free, no registration required.

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!

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
add comment

2 Answers

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
  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
share|improve this answer
    
Nice! Fist example is exactly what I needed. –  NReilingh May 17 '12 at 19:34
add comment

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.

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
add comment

Your Answer

 
discard

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.