Hey Brian,
After a "ghost email" from that "smart-aleck developer"(Oh my!
Phew!), the code below is much better! Hehe.
UPDATE m
SET Region = t.Region, DEPTNO = t.DEPTNO, DNAME = T.DNAME, LOC = t.LOC
FROM Dept2 m
JOIN Dept t ON m.DeptNo = t.DeptNo
WHERE m.Region <> t.Region OR m.DEPTNO <> t.DEPTNO OR t.REGION <>
t.REGION
insert into dept2 (deptno, dname, loc, region)
select t.deptno, t.dname, t.loc, t.region
from dept t
left join dept2 m
on m.deptno = t.deptno
where m.deptno is null;
-----Original Message-----
From: Edwards Ed
[mailto:mssqldba-ezmlmshield-x75626803.[Email address protected]
Sent: Friday, April 27, 2007 8:57 AM
To: LazyDBA Discussion
Subject: SQL Server 2005 Merge Statement
Hey Brian,
Your question "Does the following statement work in SQL Server 2005"?
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)
The answer is no. There is not "MERGE" statement in SQL Server 2005.
However, you can simulate the Oracle and DB2 Merge statement in SQL
Server 2000 and 2005 using the code below. Hehe.
--SQL SERVER MERGE (Simulate Oracle and DB2 Merge statement)
UPDATE M
SET M.REGION = T.REGION
FROM DEPT2 M
INNER JOIN DEPT T
ON M.DEPTNO = T.DEPTNO
INSERT INTO DEPT2 (DEPTNO, DNAME, LOC, REGION)
SELECT T.DEPTNO, T.DNAME, T.LOC, T.REGION
FROM DEPT T
LEFT JOIN DEPT2 M
ON M.DEPTNO = T.DEPTNO
WHERE M.DEPTNO IS NULL;
DEPT TABLE
Deptno Dname Loc Region
10 ACCOUNTING NEW YORK AA
20 RESEARCH DALLAS BB
30 SALES CHICAGO CC
40 OPERATIONS BOSTON DD
DEPT2 TABLE
Deptno Dname Loc Region
20 RESEARCH DALLAS NULL
40 OPERATIONS BOSTON NULL
After running the SQL code above, the DEPT2 table should look like the
following below.
DEPT2 TABLE
Deptno Dname Loc Region
10 ACCOUNTING NEW YORK AA
30 SALES CHICAGO CC
20 RESEARCH DALLAS BB
40 OPERATIONS BOSTON DD
It's Fridayyyyyyy! Partyyyyyyyyyyyy! Hehe.
Have a great Day and Weekend!
Please take a few minutes to provide feedback on the quality of service
you received from our staff. The Department of Education values your
feedback as a customer. Commissioner of Education Jeanine Blomberg is
committed to continuously assessing and improving the level and quality
of services provided to you.Simply use the link below. Thank you in
advance for completing the survey.
http://data.fldoe.org/cs/default.cfm?staff=Ed.[Email address protected]
---------------------------------------------------------------------
TO REPLY TO EVERYBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
MS Sql Server LazyDBA home page