Francisco H Tapia
my.lists at verizon.net
Tue Feb 25 10:35:37 CST 2003
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