March 23, 2004 at 7:30 am
Is there a command in sql server similiar to the command "Merge into" from Oracle.
I need to update records in a table and also insert new record into this table if they don't exist. Example from Oracle
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)
March 24, 2004 at 4:30 am
No, You would have to do the work in 2 sql batches UPDATE - INSERT
Something like...
UPDATE employee SET
salary = et.salary,
bonus = et.bonus,
comm = et.comm
FROM
emp_temp et
WHERE
employee.empno = et.empno -- Assumes empno PrimaryKey
AND( employee.salary <> et.salary OR -- Watch out for NULL!
employee.bonus <> et.bonus OR
employee.comm <> et.comm )
INSERT employee(empno, firstnme, midinit, lastname, workdept, phoneno,
hiredate, job, edlevel, sex, birthdate, salary, bonus,
comm)
SELECT 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
FROM
emp_temp et
WHERE
NOT EXISTS( SELECT empno FROM employee
WHERE empno = et.empno )
/rockmoose
You must unlearn what You have learnt
May 29, 2009 at 11:41 am
the feature exists in sql server 2008 ,now
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply