0

I would really like to find some kind of automation on this issue I am facing; A client has had a database attached to their front end site for a few years now, and until this date has been inputting certain location information as a numeric code (i.e. County/State data).
They now would like to replace these values with their corresponding nvarchar values. (e.g Instead of having '8' in their County column, they want it to read 'Clermont County' etc etc for upwards of 90 separate entries).
I have been provided with a 2-column excel sheet, one with the old county numeric code and one with the text equivalent they request. I have imported this to a temp table, but cannot find a fast way of iteratively matching and updating these values.
I don't really want to write a 90 line CASE WHEN paragraph and type out each county name manually. Opens doors for human error etc.

Is there something much simpler I don't know about what I can do here?

PatientZero
  • 1
  • 1
    Load the Excel table in the database and use a join plus an update statement – Kyle Feb 13 '17 at 17:43
  • Well that temp table probably should be an actual table that you reference so you don't have to keep loading it every time. Or, just update the source table with the reference temp table via a join. [Here is an example](http://stackoverflow.com/questions/982919/sql-update-query-using-joins). In general, when working in SQL only think about "looping" as a last resort. – S3S Feb 13 '17 at 17:46
  • I think it's call an inner join. – shawnt00 Feb 13 '17 at 18:14

1 Answers1

1

I realize that it might be a bit late, but just in case someone else is searching and comes across this answer...

There are two ways to handle this: In Excel, or in SQL Server.

1. In Excel

Create a concatenated string in one of the available columns that meets your criteria, i.e. =CONCATENATE("UPDATE some_table SET some_field = '",B2,"' WHERE some_field = ",A2)

You can then auto-fill this column all the way down the list, and thus get 90 different update statements which you can then copy and paste into a query window and run. Each one will say 

UPDATE some_table SET some_field = 'MyCounty' WHERE some_field = X

Each one will be specific to a case; therefore, you can run them sequentially and get the desired result, or...

2. In SQL Server

If you can import the data to a table then all you need to do is write a simple query with a JOIN which handles the case, i.e.

UPDATE T1
SET T1.County_Name = T2.Name
FROM Some_Table T1 -- The original table to be updated
INNER JOIN List_Table T2 -- The imported table from an Excel spreadsheet
    ON T1.CountyCode = T2.Code
;

In this case, Row 1 of your original Some_Table would be joined to the imported data by the County_Code, and would update the name field with the name from that same code in the imported data, which would give you the same result as the Excel option, minus a bit of typing.

3BK
  • 1,338
  • 1
  • 8
  • 11