Customer Table data update

Transact-SQL
1

I have a master table CustomerData with the column names CustId, CustName, Phone. It has almost 1 lac records. Recently my boss sent me a excel containing 100 records having the updated phone numbers of some customers. This excel has two columns CustID and NewPhone.
How to update the phone numbers of customers in CustomerData table using the data in excel doc? Is there any way using Tsql update?

2 
update a
   set a.phone=b.newphone
  from customerdata as a
       inner join newcustomerdata as b
               on b.custid=a.custid
              and b.newphone!=a.phone
;
1 Like
3

For extra safety - you can backup the CustomerData table prior to making any changes.

SELECT * INTO dbo.CustomerData_Backup_20180225 FROM dbo.CustomerData;

This will insure that you can recover is something goes wrong.

You need to either open the excel file directly (OPENROWSET), load the data to a temp table - or build individual update statements.

For a one time operation - I would just build the individual update statements in Excel, copy and paste the update statements into SSMS and execute.

In Excel - in a column to the right of the data use something like this (assuming row 1 has column headers):

="Update dbo.CustomerData Set Phone = '" & C2 & "' Where CustId = " & A2 & ";"

Then copy/paste this formula into every row in the Excel spreadsheet. Once you have that, copy/paste the results into SSMS and execute. For safety - wrap this in a transaction to test first...for example:

BEGIN TRANSACTION;

Update dbo.CustomerData Set Phone = '123-123-1234' Where CustId = 1234;

ROLLBACK TRANSACTION;

Add code to validate the update was successful within the transaction (e.g. before/after view of customer data). Once satisfied that the updates are working as expected - run without the transaction to commit the changes to the database.

Another option:

Use Excel to build an insert statement to a temp table - then use the temp table in an update statement. For example - in SSMS start with this:

DECLARE @phoneUpdate TABLE (CustId int, NewPhone char(12));
INSERT INTO @table (CustId, Phone)
VALUES (

Now - in excel build this formula: =" , (" & A2 & ", '" & C2 & "')"

Put that formula into every row - then copy/paste the row values - this is the code you end up with:

DECLARE @phoneUpdate TABLE (CustId int, Phone char(12));
 INSERT INTO @phoneUpdate (CustId, Phone)
 VALUES (
--*** Insert the values from Excel - make sure you remove the comma from the first row
       (1234, '123-123-1234')
     , (1235, '123-123-1235')
     , (1236, '123-123-1236')
     , (1237, '123-123-1237')
--*** End of insert from Excel
       );

 --==== Show Before
 SELECT *
   FROM dbo.CustomerData cd
  INNER JOIN @phoneUpdate pu ON pu.CustId = cd.CustId;

 --==== Start transaction
  BEGIN TRANSACTION;

 UPDATE cd
    SET cd.Phone = pu.Phone
   FROM dbo.CustomerData cd
  INNER JOIN @phoneUpdate pu On pu.CustId = cd.CustId;

 --==== Show Results of Update
 SELECT *
   FROM dbo.CustomerData cd
  INNER JOIN @phoneUpdate pu ON pu.CustId = cd.CustId;
 
 --==== Rollback/Commit transaction
        ROLLBACK TRANSACTION;
        --COMMIT TRANSACTION;

Once you are satisfied with the update - you then comment out the ROLLBACK and un-comment the COMMIT to make the changes permanent.

This pattern works well when you don't have a lot of rows in Excel. Once you have more than a couple hundred rows then you should change the approach to importing the Excel data directly to a staging table. Once you have the data in that staging table - the rest of the update works the same way.