paul.hartland at fsmail.net
paul.hartland at fsmail.net
Tue Feb 25 11:00:00 CST 2003
Many thanks for everyones help today, got it sorted..........
I can't thank this list enough.............
Paul
From: "Marcus, Scott (GEAE, RHI Consulting)" <scott.marcus at ae.ge.com>
Date: Tue 25/Feb/2003 16:56 GMT
To: "'accessd at databaseadvisors.com'" <accessd at databaseadvisors.com>
Subject: RE: [AccessD] Re: [dba-SQLServer]SQL Server 7 - Stored Procedure
Help - Quite Urgent.
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
_______________________________________________
AccessD mailing list
AccessD at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/accessd
Website: http://www.databaseadvisors.com
__________________________________________________________________________
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