2 Replies Latest reply on Dec 8, 2015 1:01 PM by WolfShade

    Inserting records in two different tables at the same time?


      Hello everyone, I have question about inserting records in two different tables at the same time, what I'm looking for is passing unique id that gets created in first insert statement to second insert statement. Example for this problem:


      <cfquery name="addRecords1" datasource="test">

            Insert Into Table1(Name, Date, Age)

            Values (<cfqueryparam cfsqltype="cf_sql_char" value="#arguments.Name#">,

                        <cfqueryparam cfsqltype="cf_sql_date" value="#arguments.Date#">,

                        <cfqueryparam cfsqltype="cf_sql_int" value="#arguments.Age#">);

            Select SCOPE_IDENTITY() As RecID;



        <cfquery name="addRecords2" datasource="test">

             Insert Into Table2(Company,City,Date,ID)

             Values(<cfqueryparam cfsqltype="cf_sql_char" value="#arguments.Company#">,

                        <cfqueryparam cfsqltype="cf_sql_char" value="#City#">,

                        <cfqueryparam cfsqltype="cf_sql_date" value="#Date#">,

                        <cfqueryparam cfsqltype="cf_sql_int" value="How to pass RecID to be inserted in this table???">);



      In this example I'm inserting records in table 1 and creating SCOPE IDENTITY as RecId. I would like to pass that id and insert in my table 2. That Id I will use  in my second table as ID. If anyone knows anything about this please let me know. Thank you.