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

Marcus, Scott (GEAE, RHI Consulting) scott.marcus at ae.ge.com
Tue Feb 25 10:57:00 CST 2003


Francisco,

The B alias is not in the subquery(so it shouldn't be a problem)...

But NOT EXISTS works also and is faster.


What I propossed...
INSERT INTO [Table A] (payroll , jobdate, jobtype)
SELECT B.payroll, B.jobdate, B.jobtype
FROM [Table B] as B
WHERE B.id NOT IN (SELECT BB.id
                   FROM [Table B] as BB, [Table A] as A
                   WHERE BB.payroll = A.payroll
                     AND BB.jobdate = A.jobdate
                     AND BB.jobtype = A.jobtype)

Scott Marcus

-----Original Message-----
From: Francisco H Tapia [mailto:my.lists at verizon.net]
Sent: Tuesday, February 25, 2003 11:36 AM
To: dba-sqlserver at databaseadvisors.com; AccessD;
dba-VB at databaseadvisors.com
Subject: [AccessD] 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
:


_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com



More information about the AccessD mailing list