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

Paul,

The following is untested and may need tweaking, but I think it will at least
get you started...

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: paul.hartland at fsmail.net [mailto:paul.hartland at fsmail.net]
Sent: Tuesday, February 25, 2003 4:43 AM
To: dba-SQLServer at databaseadvisors.com; accessd at databaseadvisors.com;
dba-vb at databaseadvisors.com
Subject: [dba-VB] 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-VB mailing list
dba-VB at databaseadvisors.com
http://databaseadvisors.com/mailman/listinfo/dba-vb
http://www.databaseadvisors.com