Skip to main content
N
New_York_Guy
Known Participant
December 11, 2008
Question

sql insert question

  • December 11, 2008
  • 1 reply
  • 277 views
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 ?
    This topic has been closed for replies.

    1 reply

    N
    Newsgroup_User
    Inspiring
    December 11, 2008
    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.
    J
    JR__Bob__Dobbs-qSBHQ2
    Inspiring
    December 11, 2008
    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?
    Terms & ConditionsAccessibility statement
    Using the community Terms of use Privacy Cookie preferences Do not sell or share my personal information ad choicesAdChoices