Multiple updates in SQL Server

Question

I have 500 records in Excel and it contains some columns which is same as the columns in SQL DB Table. I have old column value as well as new column value in excel.

Example

Excel

Name Age OldEmployeeID NewEmployeeID

x    1    100          200
y    2    101          201  
z    3    102          202

SqlTable

EmployeeTable

Name Age Department City EmployeeID
x    1     HR        x    100
a    4     HR        x    103
y    2     Admin     x    101
b    5     Finance   x    104
c    3     IT        x    102

I want to update EmployeeID column in SQL Table as NewEmployeeID which is there in excel. Can anyone suggest how to write a sql query to update the sql table.

Is EmployeeID an identity column?

BJones
– BJones

2016-06-20 17:11:03 +00:00
Commented Jun 20, 2016 at 17:11 — BJones
– BJones, Commented Jun 20, 2016 at 17:11
not an identity column

RCM
– RCM

2016-06-20 17:17:32 +00:00
Commented Jun 20, 2016 at 17:17 — RCM
– RCM, Commented Jun 20, 2016 at 17:17

Arleigh Reyna · Accepted Answer · 2016-06-20 17:23:40Z

1

Assuming the column is not an ID Column or having some other constraint you can:

Option 1:

Assuming your table is similar to the screenshot you have a concatenated column with this formula and drag it down and copy it directly into SQL.

=CONCATENATE("UPDATE dbo.EmployeeTable SET EmployeeID = ",D2," WHERE EmployeeID = ",C2,";")

Option 2:

You can import the Excel file into SQL and use a statement like the one below. However, be cautious if any of the old EmployeeIDs overlap the new EmployeeIDs. For Example if Jim has ID 100 and his new one is 500 and Jane's new ID is 100 and if you accidentally run it a second time, the Jane will also get an ID of 500.

UPDATE EmployeeTable SET EmployeeID = Excel.NewEmployeeID
FROM
    EmployeeTable
    JOIN ExcelTable
        ON  Excel.OldEmployeeID = EmployeeTable.EmployeeID
;

answered Jun 20, 2016 at 17:23

Arleigh Reyna

3691 silver badge6 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

BJones Over a year ago

You can also specify multiple ON criteria to mitigate chances of overlap. by adding AND i.e. ON Col1 = Col2 AND Col3 = Col4...

Collectives™ on Stack Overflow

Multiple updates in SQL Server

Excel

EmployeeTable

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

Excel

EmployeeTable

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related