how can i insert list of ids while adding records?

I have a list of question ids to be inserted into an answers table while adding records.

<!--- answers query --->
<cfquery name="answers">
    SELECT answer, rank 
    FROM answers 
    WHERE question_id IN (<cfqueryparam  cfsqltype="cf_sql_integer" list="true" separator="," value="#qid#">)                       
</cfquery>

the qid is the list of old question id through which I am retrieving answers and then I am duplicating the same answers with newer question id.

<!--- list of questionids --->
<cfset questionid = ArrayToList(idArray)> 
    <cfquery name="insertanswers">
        INSERT INTO answers (
            question_id, answer, rank
        )
        VALUES 
            <cfloop query="answers">
                (
                <cfqueryparam cfsqltype="cf_sql_integer" list="true" separator="," value="#questionid#">
                ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#answers.answer#">
                ,<cfqueryparam cfsqltype="cf_sql_numeric" value="#answers.rank#">
                )
                <cfif answers.CurrentRow LT answers.RecordCount>,</cfif> 
            </cfloop>
    </cfquery>

While adding records I am getting column count doesn't match value count error but when I wrap code with cftry and dump it I found that it is getting two ids for each answer in form of comma delimited. Is there any way to pass only single id for each answer? I am just replacing the older question ids with newer one.

Solution

Since you need to link the old and new id's, you will need a different approach than the one used here. When you generate the new question records, store the id's in a structure, rather than an array, so you can maintain a mapping of old => new values.

<!--- initialize mapping of old => new ids --> 
 <cfset idMapping = {}>

  <cfloop ...>

         <!--- use the "result" attribute to capture the new id --->
         <cfquery result="addRecord" ....>
           INSERT INTO YourTable (...) VALUES (...);
         </cfquery>

         <!--- save the id in the array -->
         <cfset idMapping[ oldQuestionID ] = addRecord.GENERATED_KEY>

 </cfloop>

When you insert the new answers, use the old id to do a look up and grab the new question id from the structure. This needs validation, but here is the general idea.

Note: To ensure data integrity, both query blocks should be enclosed in a single <cftransaction>

   <cfquery name="insertanswers">
        INSERT INTO answers (
            question_id, answer, rank
        )
        VALUES 
            <cfloop query="answers">
                (
                <!--- get the new id from the structure --->
                <cfqueryparam cfsqltype="cf_sql_integer" value="#idMapping[ oldQuestionID ]#">
                ,<cfqueryparam cfsqltype="cf_sql_varchar" value="#answers.answer#">
                ,<cfqueryparam cfsqltype="cf_sql_numeric" value="#answers.rank#">
                )
                <cfif answers.CurrentRow LT answers.RecordCount>,</cfif> 
            </cfloop>
    </cfquery>

Update 1:

Here is a complete example:

SQL
CF Code

Update 2:

If this is a regular occurrence, I would suggests a different approach that would eliminate the looping. Add a UUID column to the main table (used to identify the new records). Then use a temp table (with an auto populating UUID column) to store the records you want to transfer. It is not as complicated as it sounds ..

(I do not have the MySQL syntax handy, so this is for SQL Server, but the overall concept is the same).

    -- use DEFAULT to automatically generate a UUID for each record
    CREATE TABLE #NewQuestions ( 
        , OldQuestionID int
        , TheUUIDColumn uniqueidentifier DEFAULT(NewID())
    )

    --- insert the records you want to transfer  
    INSERT INTO #NewQuestions ( OldQuestionID )
    SELECT  QuestionID
    FROM    Questions
    WHERE   .....

Next, use a JOIN to insert those questions back into the main table. Note, how it stores the UUID so we can identify the new records later.

    INSERT INTO Questions( TheUUIDColumn, Question, ... )
    SELECT  tmp.TheUUIDColumn, q.Question, ....
    FROM    Questions q INNER JOIN #NewQuestions tmp 
                 ON tmp.OldQuestionID = q.QuestionID

Finally, use the UUID to identify both the old and new id's and insert the related "answers".

    INSERT INTO answers ( QuestionID, Answer, ....)
    SELECT q.QuestionID, a.Answer
    FROM   Questions q 
                INNER JOIN #NewQuestions tmp ON tmp.TheUUIDColumn = q.TheUUIDColumn
                INNER JOIN answers a ON a.QuestionID = tmp.OldQuestionID

The temp table approach offers greater control. It is also set based, which is much more efficient than processing one record at a time via a loop.