Stored Procedure

D

directmail

Hello,

I am a newbie ..

I want to create a stored procedure, which will do a select <columns> from
one table and insert them into another. I expect some index violations - I
want to catch these exceptions and insert the error records into another log
table. The end user needs to look at these tables.

I am getting stuck on :

------------------------
CREATE PROCEDURE sp_insert_property_records
as
select * from property_information;
--------------------------

I get an error saying that the create table syntax is invalid. I am using
the ACCESS SQL view to do this stuff.

I would appreciate if you could tell me what the error is ( if possible
provide a link to the basic stored procedures tutorial ) and also give me
some information on exception handling.

Thanks !
Girish
 
L

Larry Linson

Access, by default, installs and uses the Jet database engine. Jet does not
support stored procedures. Saved queries can often be used instead.

Larry Linson
Microsoft Access MVP
 
A

A C

Thanks !

How do I catch exceptions in reqular sql queries ?
Click to expand...

Well it sort of depends what you are asking here by "catching exceptions".
In Access an exception is a thrown error, as an example you got an exception
when you tried to use invalid Access sql. I think what you are meaning
however is records which cannot be inserted?

What you could do is write two queries, the first which matches (joins) the
records between the tables and only inserts the corresponding data where
there is a match, and then another which returns all the records *not*
matched by the first query (you will save this result set into a table for
the user to view later).

As a dummy example, lets assume you have two tables, a master table where
you are trying to insert the records into, and a working table which has
some valid data for insertion and some "problem" data that cannot be
inserted into the master table.
Lets assume that the columns you are wanting to insert are called col1 and
col2 in both tables, it trivially easy to change the sql to use non-matching
names. Lets also assume that the index in both tables which will be matched
is called colIndex.

The sql to add the field data from the working table into the master table
is:
UPDATE working_table INNER JOIN master_table ON working_table.colIndex =
master_table.colIndex SET master_table.col1 = [working_table].[col1],
master_table.col2 = [working_table].[col2];

All records in the master table which had a matching record in the working
table will now have their col1 and col2 updated to the values in the working
table. *NOTE* If you have *multiple* records for the same index in the
working table Access will not know which to use and just uses one of them.
This wont be a problem if your working table didnt allow/contain duplicates.

The sql to find non-matching records between the working table and the
master table, and to put them into a new table called noMatchTable is:
SELECT working_table.* INTO noMatchTable
FROM working_table LEFT JOIN master_table ON working_table.colIndex =
master_table.colIndex
WHERE (((master_table.colIndex) Is Null));

Hope this is what you were after. If not post back and we can help with
your actual problem :)

Regards
A
 
Top