如何在添加记录时插入ids列表？ [英] how can i insert list of ids while adding records?
问题描述
我有一个问题ID列表，在添加记录时插入答案表。
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>
qid id我通过它检索答案，然后我重复相同的答案与较新的问题id。
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>
添加记录时，我会得到列计数与值计数不匹配错误，但当我包装代码与 cftry 和转储它，我发现它得到两个ids为每个答案以逗号分隔的形式。有没有办法只传递单个id的每个答案？
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.
推荐答案
由于您需要链接旧的和新的ID，需要与此处使用的不同的方法。当生成新的问题记录时，将id存储在结构中而不是数组中，以便您可以维护old =>新值的映射。
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>
插入新答案时，使用旧ID查找并抓取新问题id从结构。这需要验证，但这里是一般的想法。
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.
注意：为了确保数据完整性，两个查询块都应包含在单个< 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>
更新1：
以下是完整的示例：
- SQL
- CF Code
更新2：
如果这是定期发生，我建议采用不同的方法，循环。将UUID列添加到主表（用于标识新记录）。然后使用临时表（使用自动填充UUID列）存储要传输的记录。它不是那么复杂，因为它的声音..
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 ..
（我没有MySQL语法方便，所以这是为SQL Server，但总体概念是一样的） 。
(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 .....
接下来，使用JOIN将这些问题插入主表。注意，它如何存储UUID，以便我们以后能识别新的记录。
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
最后，使用UUID来标识旧的和新的id，并插入相关的答案。
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.
这篇关于如何在添加记录时插入ids列表？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
