sql insert question

[New_York_Guy]

I am trying to do a simple insert :
<cfquery name="qryInsert" datasource="dbName">
insert into table
(primaryID,
partNumber,
txnDate
quantity,
lineItem)

values(
'#primaryID#',
'#form.partNumer#',
'#form.txnDate#',
'#form.quantity#".
'#form.lineItem#')
</cfquery>

What I want/need to do is to check to see if the lineItem is aleady in the table. If it is there, then that record will not be inserted. If the line item is not in the table, then insert the record.

What is the propery sql syntax to check for this, and where would it go, inside this query/insert ?

[Newsgroup_User]

New York Guy wrote:

> What is the propery sql syntax to check for this, and where would it go,
> inside this query/insert ?
>

You have two choices, you could put a unique index on the lineItem field
in the database, then the database will throw an error if any duplicate
lineItem value is attempted to be inserted. A <cftry><cfcatch> can
easily be used to capture this exception and handle it appropiatly.

OR

You can query the database with a separate select query and then make an
if comparison to see whether to insert the record or not depending on if
a record is returned in the first query.

[JR__Bob__Dobbs-qSBHQ2]

You might wrap your SQL logic in a stored procedure that is responsible for checking for the existence of record before running the insert.

What database (version and vendor) are you using?