August 31, 2015 at 9:38 am
I have the following scenario:
We have a current database table (PAF) that had a new column added to it named 'Email'. This table also has some other columns including one named [Employee Number].
We also have an Excel spreadsheet that has 2 columns 'Employee Number' and 'E-mail Address'. I need to take the E-mail Address field from the spreadsheet, match it up with the employee number between the spreadsheet and PAF table, and then insert the email address into the database column.
From what I've read, I'm guessing I would do this using a MERGE statement, correct? If that is correct, can anyone give me a good example of how to accomplish this? All the examples I have read so far are a little more technical than I was expecting. If that is not correct, what would the recommended way to do this be? Thank you for your help in advance.
August 31, 2015 at 9:55 am
It depends... Are you trying to add new employees to the table (There are employees that exist on the spreadsheet but not in the table)?
If not, there's no need for a MERGE... A good ol fashion UPDATE will do the trick.
August 31, 2015 at 10:09 am
Thank you for your quick reply. If I use an UPDATE statement for this, how do I tell it to match on the employee number between the spreadsheet and the table? Also, what is the correct way to reference the spreadsheet? When I use the SQL Server Import and Export wizard and specify the query to use and use the below statement, it says 'This SQL statement is not a query'. The name of the workbook is Book1.xlsx.
UPDATE PAF
SET PAF.Email = [E-mail Address]
WHERE PAF.[Employee Number] = [Employee Number]
August 31, 2015 at 10:11 am
You'll need to import the spreadsheet into a table first. Use the Import/Export wizard for that. Once you've got the data in a table, use the UPDATE that Jason mentioned.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
August 31, 2015 at 11:03 am
If you don't want to import the spreadsheet, another "Quick & Dirty" option is to create an Excel formula like this...
="(" & A1 & ", '" & B1 & "'),"
Then you can copy/paste the formula result into SSMS code that looks like this...
UPDATE e SET e.Email = x.Email
FROM dbo.Employees e
JOIN (VALUES
<paste Excel formula results here>
) x (EmployeeID, Email)
ON e.EmployeeID = x.EmployeeID;
It'll look like this when the excel formula is pasted...
UPDATE e SET e.Email = x.Email
FROM dbo.Employees e
JOIN (VALUES
(1, 'name1@Email.com'),
(2, 'name2@email.com'),
(3, 'name2@email.com')-- Make sure to remove the last coma
) x (EmployeeID, Email)
ON e.EmployeeID = x.EmployeeID;
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply