Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium


SQL Update table with list of data

Posted on 2012-08-30
Medium Priority
Last Modified: 2012-09-02

I have a list of social security numbers that need to be updated to my table.  There are about 2000 records that need to be updated.  Any ideas on how to do this efficiently?
Question by:Jasmin01
LVL 66

Expert Comment

by:Jim Horn
ID: 38351971
Assuming you have access to SQL Server SSMS, the easiest way would be to just write an UPDATE statement (holy God make a backup of your table first), something like...

UPDATE YourTable
SET SSN = Whatever
Where SomeDangThing = SomeValue

Please define whether or not you need to do this through an application, and what 'efficiently' means.
LVL 13

Expert Comment

by:Jesus Rodriguez
ID: 38352138
- Which one will be the criteria to update the SSN?
- Wich format is your list .txt, csv,XML an Sql Table??
LVL 16

Assisted Solution

DcpKing earned 750 total points
ID: 38352401
An SSN is just a string, so you must have a list in some form with one or more criteria (name, employee id, whatever) that identify the record(s) to be changed, and the SSN to use to update the SSN field.

I'd recommend that you write the code so that it outputs a note of record number (or primary key), old SSN value, and new SSN value for each record that it changes - heaven may not save you if you mess it up and can't get back!


Old data:
empl ID    forename     surname      SSN
001               Adam            None       001-01-0001
002               Eve                None       001-01-0002

Update table:
empl ID       NewSSN
001                001-02-0001
002                001-02-0002

--  ------------------------------
begin transaction
    select ot.[empl ID], ot.forename, ot.surname, ot.SSN, nt.[empl ID], nt.NewSSN
    from OldTable ot
    inner join NewTable nt
    on ot.[empl ID] = nt.[empl ID]
    --  ------------------------------
    update ot
        set ot.SSN = nt.NewSSN
    from OldTable ot
    inner join NewTable nt
    on ot.[empl ID] = nt.[empl ID]
End Transaction
--  ------------------------------

Open in new window

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.


Author Comment

ID: 38353110
The file is in an Excel format.
LVL 16

Expert Comment

ID: 38353193
"The file is in an Excel format. "

2000 records aren't many. Make yourself a table in SSMS with the right structure and you can copy them all and just paste them int to an open "Edit top 200 rows" window on the empty table!

Then it'll be easier to do the work in a repeatable way and also to save the list for later reference (when someone says "I never told you to change the CEO's SSN to 999-99-9999 :)   )

Author Comment

ID: 38353201
Sorry, but I have to send my query to a DBA to run, so is there a query to copy all the files from Excel and then paste into he table?
LVL 143

Accepted Solution

Guy Hengel [angelIII / a3] earned 750 total points
ID: 38353598
presuming that in your excel file, you have both the "primary key" value of the current table to be updated in column A, and the other field's value that is to be updated in column B, create a small formula in column C, for example row 2 (presuming the excel file has the header row 1):
C2:   = "UPDATE yourtable SET col_to_be_updated = '" & B2 & "' WHERE key_column = " & A2 & ";"

now, this formula presumes that the key_column is numerical (otherwise add the single quotes also like around the B2 part), and the the col to be updated is a varchar field.

if the B2 values contain quotes, we need to modify slightly to make sure the quotes won't break the script:
C2:   = "UPDATE yourtable SET col_to_be_updated = '" & SUBSTITUTE(B2, "'", "''")& "' WHERE key_column = " & A2 & ";"

fill down that formulate down for all the 2000 rows, then copy the resulting "script" to a notepad, and send that script to your dba. the last line of the script shall be a commit ...

that is what I often do when I am requested a mass update from our end users providing me excel files.
LVL 16

Expert Comment

ID: 38354251
If the file has to be in Excel, and you're not allowed to do the import yourself, write a little SSIS package to do it and give that to the DBA. It's just one piece of dataflow, consisting of a Read from the Excel file and a Write to a SQL Server staging table. Then write a procedure to do the update from the staging table into the real one - the example above would work and you could put that code into a stored procedure and also invoke that from the SSIS, after the data is pulled in from Excel.


LVL 70

Expert Comment

by:Scott Pletcher
ID: 38354481
Your DBA can easily load the spreadsheets, just send them to him/her, you shouldn't need to load the data into a table yourself.

Featured Post

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Microsoft Access has a limit of 255 columns in a single table; SQL Server allows tables with over 255 columns, but reading that data is not necessarily simple.  The final solution for this task involved creating a custom text parser and then reading…
MSSQL DB-maintenance also needs implementation of multiple activities. However, unprecedented errors can hamper the database management. In that case, deploying Stellar SQL Database Toolkit ensures fast and accurate database and backup repair as wel…
Viewers will learn how the fundamental information of how to create a table.
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Suggested Courses

580 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question