[dba-SQLServer]SQL Server 7 - Stored Procedure Help - Quite Urgent.

NOT IN has the same side effects as NOT EXISTS.  It can be easily re-written as follows:

insert into tableA (Payroll, JobDate, JobType)
   select B.Payroll, B.JobDate, B.JobType
      from tableB B left outer join (
         select A.Payroll, A.JobDate, A.JobType
            from tableA A 
               inner join tableB tb 
                  on A.Payroll = tb.Payroll 
                  AND A.Jobdate = tb.JobDate 
                  AND A.JobType = tb.JobType
         ) t  on B.Payroll = t.Payroll 
             and B.JobDate = t.JobDate 
             and B.JobType = t.JobType
   where  t.Payroll is null 
      and t.JobDate is null 
      and t.JobType is null


______________________________________________________
Robert Djabarov
Certified MS SQL Server DBA
Certified MS SQL Server Programmer
Certified MS VB Programmer
? (210) 913-3148 - phone
( (210) 753-3148 - pager

 -----Original Message-----
From: 	Francisco H Tapia [mailto:my.lists at verizon.net] 
Sent:	Tuesday, February 25, 2003 10:36 AM
To:	dba-sqlserver at databaseadvisors.com; AccessD; dba-VB at databaseadvisors.com
Subject:	Re: [dba-SQLServer]SQL Server 7 - Stored Procedure Help - Quite Urgent.

How about ....

Insert into tableA (Payroll, jobdate, jobtype)
    Select Payroll, jobdate, jobtype
    From tableB AS B
    WHERE NOT EXISTS( Select Payroll, jobdate, jobtype
                                            From tableA AS A
                                            Where B.Payroll = A.Payroll AND
B.Jobdate = A.JobDate AND B.JobType = A.JobType)

I could have used a NOT IN clause but then NOT IN can have some serious side
effects...In General the above statement will be evaluated by the engine
where as a NOT IN could miss evaluating the B alias in the where clause and
cause it to report bad data, using Exists (or NOT EXISTS) usually yields
faster performance and the assurance that it evaluates correctly.
-Francisco
http://rcm.netfirms.com
----- Original Message -----
From: <paul.hartland at fsmail.net>
To: <dba-SQLServer at databaseadvisors.com>; <accessd at databaseadvisors.com>;
<dba-vb at databaseadvisors.com>
Sent: Tuesday, February 25, 2003 1:42 AM
Subject: [dba-SQLServer]SQL Server 7 - Stored Procedure Help - Quite Urgent.


: To all,
:
: Watch for Word Wrap...............
:
: Being quite a novice with Stored Procedures on SQL Server 7.0, I'm looking
for some pretty urgent help at the moment.  I have two tables, lets say
Table A & Table B.  Table A is my master table, and Table B is a temporary
table.  Before Table be gets populated with any data, every existing record
is deleted so I start with a fresh table so to speak.  Table B consists of 3
fields PayrollNo, JobDate, JobType (these fields are also in the master
table, but the master table is used to contain other consting information,
which is entered at a later date).
:
: What I would like is a Stored Procedure which would Insert Table B
contents into Table A, but making sure that I don't insert any duplicates
into Table A (something like an unmatched query in access but with an Insert
also).  i.e.
:
: Table A                                                       Table B
: payroll  jobdate    jobtype                                   id  payroll
jobdate    jobtype
: 999001   01/01/01   0                                          1  999001
01/01/01   0
: 999001   01/01/01   2                                          2  999001
01/01/01   1
: 999002   01/01/01   2                                          3  999001
01/01/01   2
:                                                                4  999002
01/01/01   0
:                                                                5  999002
01/01/01   1
:                                                                6  999002
01/01/01   2
:
: So when I invoke the stored procedure, it would insert records 2, 4 and 5
from Table B into Table A........Anyone any idea what I'm on about, if so
can anybody please help.......
:
: Thanks in advance
:
: Paul Hartland
:
: __________________________________________________________________________
: Freeserve AnyTime - Go online whenever you want for just £6.99 a month for
: your first 3 months, that's HALF PRICE! And then it's just £13.99 a month
: after that.
:
: For more information visit http://www.freeserve.com/time/ or call free on
: 0800 970 8890
:
:
: _______________________________________________
: dba-SQLServer mailing list
: dba-SQLServer at databaseadvisors.com
: http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
: http://www.databaseadvisors.com
:


_______________________________________________
dba-SQLServer mailing list
dba-SQLServer at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-sqlserver
http://www.databaseadvisors.com