SQL INSERT vs. UPDATE on the fly?

I need to INSERT or UPDATE a large number of records into a MS Access or SQL database on a monthly basis.

Initially (briefly before AA crashed) I was able to load using a SQL Insert/Update/Delete where the SQL command was of the form ...

IF EXISTS (SELECT * FROM Table ...
UPDATE Table SET ....
ELSE
INSERT INTO Table VALUES ....

This worked for about 20 minutes and then I started to get errors and now it fails everytime with an error that the command expected a SELECT, UPDATE, PROCEDURE, DELETE ....

I need to find a way to decide whether to insert a new record or update an existing record on the fly. How do I structure this?

1. Can I do it entirely with SQL statements using AA

OR

2. Do I have to check for the pre-existance of the record in the database using SQL SELECT from within AA and then use IF/ELSE to decide which to execute, either SQL INSERT or UPDATE? If so how to I check if the record exists?

Thank you for any guidance.

[forumsupport]

Hello,

Can you upload screenshot of error that you receive so that we can actually see and help you resolve it?

There are two ways to achieve the mentioned task,

1) Try following SQL statements,

IF EXISTS (SELECT * FROM Table ...
UPDATE Table SET ....
End if

IF NOT EXISTS (SELECT * FROM Table ...
INSERT INTO Table VALUES ....
End if

2) As you mentioned, check for the pre-existance of the record in the database using SQL SELECT from within AA and then use IF/ELSE to decide which to execute, either SQL INSERT or UPDATE. Following is the text format of commands for same.

Variable Operation : False To $Prompt-Assignment$

Connect to 'Provider=MSDASQL.1;Persist Security Info=False;Data Source=dBASE Files' Session: 'Default'

Execute SQL Statement: 'Select fieldname from tablename' with 1 records to fetch Session: 'Default'

Start Loop " Each row in a SQL query dataset of Session = Default"
Variable Operation : True To $Prompt-Assignment$
End Loop

If $Prompt-Assignment$ Equal To "True" Then
Execute SQL Statement: 'Update Tablename...' Session: 'Default'
Else
Execute SQL Statement: 'Insert into...' Session: 'Default'
End If

Hope that helps.