Stored procedure - what to do...

[sh]

Hi.

I am quite new into stored procedures written in t-sql. (MSSQL server)

I have three different tables in my DB. Two of the tables (with different
data) are updated regularly and need to be joined into a third
table that works as the basis for an application in ASP.

The two former tables each contain a field with a persons identity number
(called PNR). I need to make sure that this number in a field the
first table is also contained in a field of the second table.

In case the number (10 digits) occurs in the fields of both tables, another
test is performed to check if the number occurs in the appropriate field in
table 3.

If it does nothing should happen to table 3 - however if the number is not
found in table 3 - then an insert of the number (and related data)
should be performed.

The operation needs to be done in a stored procedure since I have to check
about 15.000 rows in each query.

In ASP the operation can be done with multiple loops on the resultsets - how
can the same operation be accomplished in a stored procedure?

Sven

[Garry]

Try this:-

INSERT INTO table3 (PNR,fld1,fld2)
SELECT table1.PNR,table1.fld1,table2.fld2
FROM table1
INNER JOIN table2 ON table1.PNR = table2.PNR
LEFT OUTER JOIN table3 on table1.PNR = table3.PNR
WHERE table3.PNR IS NULL

Not sure how efficient this is but is will work. I have assumed that
table3 will be a mixture of data between table1 and table2.

Regards

Garry

On Mon, 7 Jul 2003 13:55:27 +0200, "sh" <se**********@yahoo.com>
wrote:

Hi.

I am quite new into stored procedures written in t-sql. (MSSQL server)

I have three different tables in my DB. Two of the tables (with different
data) are updated regularly and need to be joined into a third
table that works as the basis for an application in ASP.

The two former tables each contain a field with a persons identity number
(called PNR). I need to make sure that this number in a field the
first table is also contained in a field of the second table.

In case the number (10 digits) occurs in the fields of both tables, another
test is performed to check if the number occurs in the appropriate field in
table 3.

If it does nothing should happen to table 3 - however if the number is not
found in table 3 - then an insert of the number (and related data)
should be performed.

The operation needs to be done in a stored procedure since I have to check
about 15.000 rows in each query.

In ASP the operation can be done with multiple loops on the resultsets - how
can the same operation be accomplished in a stored procedure?

Sven