merge into when matched - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

merge into when matched

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-03-24 : 08:48:09

sean writes "Is there something equivalent to this Oracle command in SQL2000. I want to merge 2 tables(Employees, Emp_temp).
First step is to insert new records into Employees when Emp_temp has different record but update Employees records when they match

MERGE INTO employee AS e
USING (SELECT
empno, firstnme, midinit, lastname, workdept, phoneno,
hiredate, job, edlevel, sex, birthdate, salary, bonus, comm
FROM emp_temp) AS et
ON e.empno = et.empno
WHEN MATCHED THEN
UPDATE SET (salary, bonus, comm) =
(et.salary, et.bonus, et.comm)
WHEN NOT MATCHED THEN
INSERT (empno, firstnme, midinit, lastname, workdept, phoneno,
hiredate, job, edlevel, sex, birthdate, salary, bonus,
comm)
VALUES (et.empno, et.firstnme, et.midinit, et.lastname,
et.workdept, et.phoneno, et.hiredate, et.job, et.edlevel,
et.sex, et.birthdate, et.salary, et.bonus, et.comm)"

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2004-03-24 : 09:34:02

Wow that's interesting Oracle syntax -- if that command isn't executing a cursor under the hood then I'd be shocked.

You need two steps in T-SQL. First, the update:

UPDATE table1
SET EmpName = table2.EmpName, Address = table2.Address, City = table2.City, ..etc...
FROM Table1
INNER JOIN Table2
ON Table1.Employee = table2.Employee

and then you need to add new rows (here's one way, you can also use NOT EXISTS or NOT IN):

INSERT INTO Table1 ( Employee, EmpName, ...etc...)
SELECT Employee, EmpName, ...etc...
FROM Table2
LEFT OUTER JOIN Table1
ON Table2.Employee = Table1.Employee
WHERE Table1.Employee is Null

- Jeff

robvolk
Most Valuable Yak

15732 Posts

Posted - 2004-03-24 : 21:58:24

quote:
if that command isn't executing a cursor under the hood then I'd be shocked.

EVERYTHING in Oracle is an under-the-hood cursor.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources