Ahhhhhh, yes! I do have Capital One! Hehe.
-----Original Message-----
From: John A DAVIS
[mailto:mssqldba-ezmlmshield-x59965140.[Email address protected]
Sent: Wednesday, December 27, 2006 11:27 AM
To: LazyDBA Discussion
Subject: RE: Simulate the Oracle and DB2 MERGE Statement in SQL Server
NOT EXISTS wins the free Coke!!!
send bill to Ed Edwards, Florida
Much better than NOT IN, I agree.
after updating skiis and bicycle, money in my wallet NOT EXISTS!!!!
>>> "Edwards Ed "
<mssqldba-ezmlmshield-x83986034.[Email address protected] 12/27/2006
8:22
AM >>>
Hey John Davis,
Not so fast! You sure you didn't break your arm by patting yourself
on the head? Hehe. Please read the response below from another
"smart
aleck developer"(Oh my! Phew!)! hehe.
Ed--
It would probably be faster to do this:
INSERT Dept2(DeptNo, DName, Loc)
SELECT DeptNo, DName, Loc
FROM Dept d
WHERE NOT EXISTS
(SELECT *
FROM Dept2
WHERE DeptNo = d.DeptNo)
A NOT EXISTS is faster than NOT IN, as it can stop searching for
values
after the first hit.
Oh my! Phew! Ouch! Bang, bang! Hehe.
-----Original Message-----
From: Edwards Ed
[mailto:mssqldba-ezmlmshield-x28490637.[Email address protected]
Sent: Wednesday, December 27, 2006 11:16 AM
To: LazyDBA Discussion
Subject: RE: Simulate the Oracle and DB2 MERGE Statement in SQL Server
Hey John Davis,
There is only one thing worst than a "developer"(Oh my! Phew!) and
that is a "smart developer" (Oh my! Phew!)! hehe.
Have a great Day!
-----Original Message-----
From: John A DAVIS
[mailto:mssqldba-ezmlmshield-x19633723.[Email address protected]
Sent: Wednesday, December 27, 2006 11:05 AM
To: LazyDBA Discussion
Subject: RE: Simulate the Oracle and DB2 MERGE Statement in SQL Server
you know it's the end of the world when programmers like me start
writing queries for Ed Edwards. . . . . . .
This is a very hard email to type.
(just sprained my arm patting myself on the back)
>>> "Edwards Ed "
<mssqldba-ezmlmshield-x75655601.[Email address protected] 12/27/2006
7:57
AM >>>
John Davis,
I was just getting ready to correct you! Hehe.
-----Original Message-----
From: John A DAVIS
[mailto:mssqldba-ezmlmshield-x49512312.[Email address protected]
Sent: Wednesday, December 27, 2006 10:50 AM
To: LazyDBA Discussion
Subject: Re: Simulate the Oracle and DB2 MERGE Statement in SQL Server
Correction, I named the table "DETPNO" and it should be DEPT
INSERT INTO DEPT2 (deptno,dname,loc)
SELECT deptno,dname,loc
>>>>>>>>>>>FROM DEPTNO <<<<<<<<this should be DEPT
WHERE DEPTNO NOT IN(SELECT DEPTNO FROM DEPT2) <<<<<<<<<<<this should
be DEPT2
>>> "Edwards Ed "
<mssqldba-ezmlmshield-x10265345.[Email address protected] 12/27/2006
7:14
AM >>>
--The DEPT table has 4 records in it
DEPTNO DNAME LOC
10 ACCOUNTING NEW YORK
30 SALES CHICAGO
20 RESEARCH DALLAS
40 OPERATIONS BOSTON
--The DEPT2 table has 2 records in it
DEPTNO DNAME LOC
20 RESEARCH DALLAS
40 OPERATIONS BOSTON
--After running this code, the DEPT2 table should be identical to DEPT
table
update m
set deptno = t.deptno
from dept2 m
inner join dept t
on m.deptno = t.deptno
insert into dept2 (deptno, .dname, loc)
select t.deptno, t.dname, t.loc
from dept t
left join dept2 m
on m.deptno = t.deptno
where m.deptno is null;
Please take a few minutes to provide feedback on the quality of
service
you received. The Department of Education values your feedback as a
customer. Commissioner John L. Winn is committed to continuously
assessing and improving the level and quality of services provided to
you by Department staff. 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
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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
---------------------------------------------------------------------
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