使用多个select语句[duplicate]批量插入

This question already has an answer here:

这个问题已经有了答案:

mySQL query: How to insert with UNION? 1 answer
mySQL查询:如何使用UNION进行插入?1回答

I am using php & mysql. I have a table called "summary" and dont want to use insert statement inside for-each loop. So What I have written the following code.

我用的是php和mysql。我有一个名为“summary”的表，不想在for-each循环中使用insert语句。我写了下面的代码。

All the values concatenated in the userData Array inside loop and appended the userData Array in the Insert Query into a variable called $sql.

所有的值都连接在循环的userData数组中，并将插入查询中的userData数组添加到一个名为$sql的变量中。

<?php
foreach ($activities as $activity) {
    $userData[] = "(SELECT A.AcctDb,'" . $default->DeftReportPeriod . "', '" . $default->DeftReportBase . "', '" . $default->DeftPeriodBasis . "', '" . $default->DeftBasisAdj . "', '" . $AcctNo . "','" . $AcctTaxId . "', '" . $RevLoc . "', '" . $YTDStart . "', '" . $CurrFreq . "', '" . $Curr . "', '" . $Ytd . "', '" . $Lastcurr . "', '" . $LastYTD . "', '" . $Last12 . "', '" . $Prior12 . "', '" . $Last12diff . "', A.AcctDateOpen, A.AcctDateClosed, A.GroupCode, A.AcctHomeLoc, A.AcctBusName, A.ClassCode, '" . $Currdiff . "', '" . $Ytddiff . "', '" . $Mon['0'] . "', '" . $Mon['1'] . "', '" . $Mon['2'] . "', '" . $Mon['3'] . "', '" . $Mon['4'] . "', '" . $Mon['5'] . "', '" . $Mon['6'] . "', '" . $Mon['7'] . "', '" . $Mon['8'] . "', '" . $Mon['9'] . "', '" . $Mon['10'] . "', '" . $Mon['11'] . "','" . $Amt['0'] . "', '" . $Amt['1'] . "', '" . $Amt['2'] . "','" . $Amt['3'] . "', '" . $Amt['4'] . "', '" . $Amt['5'] . "', '" . $Amt['6'] . "', '" . $Amt['7'] . "', '" . $Amt['8'] . "', '" . $Amt['9'] . "', '" . $Amt['10'] . "', '" . $Amt['11'] . "' FROM accounts A WHERE A.AcctDb = '" . $AcctDb . "' and A.AcctTaxId='" . $AcctTaxId . "')";
}

$sql = 'INSERT INTO summary (AcctDb, SumReportPer, SumReportBase, SumPeriodBasis, SumBasisAdj, AcctNo,AcctTaxId, SumRevLoc, SumYtdStart, SumCurrFreq, SumCurrAmt, SumYtdAmt, SumLastCurr, SumLastYtd, SumLast12, SumPrior12, SumLast12Diff, SumDateOpen, SumDateClosed, SumGroupCode, SumHomeLoc, SumBusName, SumClassCode, SumCurrDiff, SumYtdDiff, SumMon01, SumMon02, SumMon03, SumMon04, SumMon05, SumMon06, SumMon07, SumMon08, SumMon09, SumMon10, SumMon11, SumMon12, SumAmt01, SumAmt02, SumAmt03, SumAmt04, SumAmt05, SumAmt06, SumAmt07, SumAmt08, SumAmt09, SumAmt10, SumAmt11, SumAmt12) SELECT ' . implode(',', $userData);

$this->db->query($sql);
?>

What's my issue is, For the single records(printed the query), the query is getting executed.

我的问题是，对于单个记录(打印查询)，查询正在执行。

For Single Record - Query Executed Successfully

INSERT INTO summary (AcctDb, SumReportPer, SumReportBase, SumPeriodBasis, SumBasisAdj, AcctNo,AcctTaxId, SumRevLoc, SumYtdStart, SumCurrFreq, SumCurrAmt, SumYtdAmt, SumLastCurr, SumLastYtd, SumLast12, SumPrior12, SumLast12Diff, SumDateOpen, SumDateClosed, SumGroupCode, SumHomeLoc, SumBusName, SumClassCode, SumCurrDiff, SumYtdDiff, SumMon01, SumMon02, SumMon03, SumMon04, SumMon05, SumMon06, SumMon07, SumMon08, SumMon09, SumMon10, SumMon11, SumMon12, SumAmt01, SumAmt02, SumAmt03, SumAmt04, SumAmt05, SumAmt06, SumAmt07, SumAmt08, SumAmt09, SumAmt10, SumAmt11, SumAmt12)(SELECT A.AcctDb,'201310', 'R', 'C', '2', '014000956','014000956', '2200', '201211', 'M', '0', '35215.12', '10968.01', '51717.3', '35215.12', '51717.3', '-16502.18', A.AcctDateOpen, A.AcctDateClosed, A.GroupCode, A.AcctHomeLoc, A.AcctBusName, A.ClassCode, '-10968.01', '-16502.18', '', '3118.72', '2037.24', '2819.42', '1345.73', '4817.26', '8046.78', '4678.11', '59.44', '106.54', '4077.1', '4108.78','', '1', '1','1', '1', '1', '1', '1', '1', '1', '1', '1' FROM accounts A WHERE A.AcctDb = 'A' and A.AcctTaxId='014000956')

For Multiple Record - Throws an error

INSERT INTO summary (AcctDb, SumReportPer, SumReportBase, SumPeriodBasis, SumBasisAdj, AcctNo,AcctTaxId, SumRevLoc, SumYtdStart, SumCurrFreq, SumCurrAmt, SumYtdAmt, SumLastCurr, SumLastYtd, SumLast12, SumPrior12, SumLast12Diff, SumDateOpen, SumDateClosed, SumGroupCode, SumHomeLoc, SumBusName, SumClassCode, SumCurrDiff, SumYtdDiff, SumMon01, SumMon02, SumMon03, SumMon04, SumMon05, SumMon06, SumMon07, SumMon08, SumMon09, SumMon10, SumMon11, SumMon12, SumAmt01, SumAmt02, SumAmt03, SumAmt04, SumAmt05, SumAmt06, SumAmt07, SumAmt08, SumAmt09, SumAmt10, SumAmt11, SumAmt12)(SELECT A.AcctDb,'201310', 'R', 'C', '2', '014000956','014000956', '2200', '201211', 'M', '0', '35215.12', '10968.01', '51717.3', '35215.12', '51717.3', '-16502.18', A.AcctDateOpen, A.AcctDateClosed, A.GroupCode, A.AcctHomeLoc, A.AcctBusName, A.ClassCode, '-10968.01', '-16502.18', '', '3118.72', '2037.24', '2819.42', '1345.73', '4817.26', '8046.78', '4678.11', '59.44', '106.54', '4077.1', '4108.78','', '1', '1','1', '1', '1', '1', '1', '1', '1', '1', '1' FROM accounts A WHERE A.AcctDb = 'A' and A.AcctTaxId='014000956'),(SELECT A.AcctDb,'201310', 'R', 'C', '2', '014000956','014000956', '2201', '201211', 'M', '0', '13.86', '0', '15.22', '13.86', '15.22', '-1.36', A.AcctDateOpen, A.AcctDateClosed, A.GroupCode, A.AcctHomeLoc, A.AcctBusName, A.ClassCode, '0', '-1.36', '', '', '', '8.89', '', '4.97', '', '', '', '', '', '','', '', '','1', '', '1', '', '', '', '', '', '' FROM accounts A WHERE A.AcctDb = 'A' and A.AcctTaxId='014000956')

The error is #1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '(SELECT A.AcctDb,'201310', 'R', 'C', '2', '014000956','014000956', '2201', '2012' at line 1****

错误是#1064 - SQL语法中有错误;检查与MySQL服务器版本相对应的手册，找到使用near '的正确语法(选择A)。AcctDb,' 201310 ',' R ',' C ',' 2 ',' 014000956 ',' 014000956 ',' 2201 ',‘2012’行1 * * * *

May I know, What I am doing wrong here, any input from the * community might help us to resolve this issue.

我想知道，我在这里做错了什么，来自*社区的任何信息都可以帮助我们解决这个问题。

I have added the sql fiddle http://sqlfiddle.com/#!2/7aa2467

我添加了sqlfiddle http://sqlfiddle.com/#!2/7aa2467

1 个解决方案

#1

The below post answers my solution

下面的帖子回答了我的解决方案。

mySQL query: How to insert with UNION?

mySQL查询:如何使用UNION进行插入?

I supposed to use UNION instead of (SELECT),(SELECT) @Wrikken

我应该使用UNION而不是(SELECT) (SELECT) @Wrikken

So, the correct query will be like below

因此，正确的查询将如下所示

    INSERT INTO summary (AcctDb, SumReportPer, SumReportBase, SumPeriodBasis, SumBasisAdj, AcctNo,AcctTaxId, SumRevLoc, SumYtdStart, SumCurrFreq, SumCurrAmt, SumYtdAmt, SumLastCurr, SumLastYtd, SumLast12, SumPrior12, SumLast12Diff, SumDateOpen, SumDateClosed, SumGroupCode, SumHomeLoc, SumBusName, SumClassCode, SumCurrDiff, SumYtdDiff, SumMon01, SumMon02, SumMon03, SumMon04, SumMon05, SumMon06, SumMon07, SumMon08, SumMon09, SumMon10, SumMon11, SumMon12, SumAmt01, SumAmt02, SumAmt03, SumAmt04, SumAmt05, SumAmt06, SumAmt07, SumAmt08, SumAmt09, SumAmt10, SumAmt11, SumAmt12) 
    SELECT A.AcctDb,'201310', 'R', 'D', '2', '014000956','014000956', '2200', '201211', 'M', '0', '35215.12', '10968.01', '51717.3', '35215.12', '51717.3', '-16502.18', A.AcctDateOpen, A.AcctDateClosed, A.GroupCode, A.AcctHomeLoc, A.AcctBusName, A.ClassCode, '-10968.01', '-16502.18', '', '3118.72', '2037.24', '2819.42', '1345.73', '4817.26', '8046.78', '4678.11', '59.44', '106.54', '4077.1', '4108.78','', '1', '1','1', '1', '1', '1', '1', '1', '1', '1', '1' FROM accounts A WHERE A.AcctDb = 'A' and A.AcctTaxId='014000956' 
    UNION 
    SELECT A.AcctDb,'201310', 'R', 'E', '2', '014000957','014000957', '2201', '201211', 'M', '0', '13.86', '0', '15.22', '13.86', '15.22', '-1.36', A.AcctDateOpen, A.AcctDateClosed, A.GroupCode, A.AcctHomeLoc, A.AcctBusName, A.ClassCode, '0', '-1.36', '', '', '', '8.89', '', '4.97', '', '', '', '', '', '','', '', '','1', '', '1', '', '', '', '', '', '' FROM accounts A WHERE A.AcctDb = 'A' and A.AcctTaxId='014000956'

#1