Hey Brian,
Dog nabit! That "smart-aleck developer"(Oh my! Phew!) is getting on
my last nerves! Hehe. Below is the final code for your question.

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 Dept2(DeptNo, DName, Loc, Region)
SELECT DeptNo, DName, Loc, Region
FROM Dept d
WHERE NOT EXISTS
(SELECT * FROM Dept2 WHERE DeptNo = d.DeptNo);



-----Original Message-----
From: Edwards, Ed
Sent: Friday, April 27, 2007 9:44 AM
To: 'Edwards Ed '; LazyDBA Discussion
Subject: RE: SQL Server 2005 Merge Statement
Importance: High

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