Check if record exists then insert new row in database table?

Question

I'm trying to improve the old code that I'm working on. The current code can be simplified and use SQL to process the insert. Here is example:

<!--- Check if user exists in Table 1 ---> 
<cfquery name="checkUser" datasource="#application.datasource#">
    SELECT user_id 
    FROM table1
    WHERE user_id = '#session.user_id#'
</cfquery>

<cfif !checkUser.recordcount>
    <cfset password = functions.encryptPsw("form.pswd") />
    <cfquery name="getInfo" datasource="#datasource#">
        SELECT fname, lname
        FROM contact
        WHERE userid = #session.user_id#
    </cfquery>

    <cfquery name="insertUser" datasource="#application.database#">
        INSERT INTO inter_work_tbl (
            user_id, 
            first_name, 
            last_name, 
            password
        ) VALUES (
            '#session.user_id#', 
            '#trim(getInfo.fname)#', 
            '#trim(getInfo.lname)#', 
            '#password#'
        )
    </cfquery>
</cfif>

I'm wondering if this can be done with Sybase SQL and use IF NOT EXISTS then run Insert statement? If anyone have good example or suggestions please let me know. Thank you.

Answer 1

I don't know/work-with coldfusion so not sure I'm reading the logic correctly ...

• if record does not exist in table1 but
• record does exit in contact then
• insert a row into inter_work_tbl

The general T-SQL query would look like (note: mixing T-SQL with references to the coldfusion variables):

insert into inter_work_tbl

      (user_id
      ,first_name
      ,last_name
      ,password)

select '#session.user_id#',
       c.fname,
       c.lname,
       '#password#'

from   contact c

where  c.userid = #session.user_id#
and    not exists(select  1
                  from    table1 t
                  where   t.user_id = c.userid)