[dba-SQLServer][AccessD] SQL Server 7.0 (Stored Procedure Help)

paul.hartland at fsmail.net paul.hartland at fsmail.net
Mon Jun 30 03:19:11 CDT 2003


To all,

I have two tables on a SQL Server(7.0), one is called tblSingleAvailability and the other tblAvailabilityAll.  What I need to do is append records from tblSingleAvailability into tblAvailabilityAll where they do not already exist, e.g. see below :

tblSingleAvailability
Payroll     AvailCode     JobDate     Type
99999         A           01/01/02    2
99999         N           04/02/02    0

tblAvailabilityAll
Payroll     AvailCode     JobDate     Type
99999         A           01/01/02    0
99999         A           01/01/02    1
99999         A           01/01/02    2
99999         N           04/02/02    1
99999         N           04/02/02    2

So I just need to append the record 99999, N, 04/02/02, 0 into tblAvailabilityAll because it does not already exist.  So I have the following SQL Stored Procedure, can anyeon see where I am going wrong as it times out whilst trying to run this.

CREATE PROCEDURE [genesis_insert_tblAvailabilityAll]
AS INSERT INTO tblAvailabilityAll (PayrollNo, JobDate, AvailabilityCode, Type)
SELECT PayrollNo, JobDate, AvailabilityCode, Type
FROM tblSingleAvailability
WHERE NOT EXISTS(SELECT PayrollNo, JobDate, AvailabilityCode,Type
FROM tblAvailabilityAll
WHERE  (tblSingleAvailability.PayrollNo = tblAvailabilityAll.PayrollNo AND
	   tblSingleAvailability.JobDate  = tblAvailabilityAll.JobDate AND 
                tblSingleAvailability.Type  = tblAvailabilityAll.Type))

Any help appreciated thanks......

Paul 








__________________________________________________________________________
Join Freeserve http://www.freeserve.com/time/

Winner of the 2003 Internet Service Providers' Association awards for Best Unmetered ISP and Best Consumer Application.


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



More information about the dba-SQLServer mailing list