1

I'm running the following SQL inside a cfquery tag

INSERT INTO Meeting_RFPMeetings (RFP_ID, Meeting_ID, Inserted, Create_UserID)

SELECT 21, 15835, {ts '2012-02-09 14:08:28'}, 42126

UNION ALL

SELECT 21, 15917, {ts '2012-02-09 14:08:28'}, 42126

UNION ALL

SELECT 21, 17052, {ts '2012-02-09 14:08:28'}, 42126

but it only inserts one row to the database. When I run the query in Query Analyzer it inserts 3 row. I'm using a loop and queryparam's in my actual statement, but even this version fails.

I'm using this code to insert data on another server and it works fine, but here even a

INSERT INTO TABLE (ID)

SELECT ID FROM TABLE1

only inserts one row.

The database server is MSSQL 2000

I tried finding a setting in my DSN but nothing stood out to me as to why it wouldn't be working

Matt Busche
  • 14,216
  • 5
  • 36
  • 61
  • If that is the *exact* code, I do not see anything wrong with it. In fact it works fine with sql 2005. How did you verify it only inserts one row? Are you sure there is nothing else limiting the rows? – Leigh Feb 09 '12 at 21:34

3 Answers3

2

Try wrapping your SELECT subqueries in parenthesis, see if that helps.

RobG
  • 1,751
  • 2
  • 11
  • 7
  • writng my SELECT line as `(SELECT 21, 15835, {ts '2012-02-09 14:08:28'}, 42126)` solved the problem. Thanks a lot Rob. – Matt Busche Feb 09 '12 at 21:39
0

I think the UNION ALL statement is the problem. Firstly, how many rows are you trying to insert at a time? Have you tried the MySQL-compatible solutions offered here:

How to Execute 2 or more insert statements using CFQuery in coldfusion?

What version of ColdFusion are you running?

Community
  • 1
  • 1
pcormier
  • 568
  • 1
  • 5
  • 15
  • Typically UNION/UNION ALL is handled as a single statement (not multiple). I have used it many times with ms sql 2005. – Leigh Feb 09 '12 at 21:29
  • UNION ALL is the correct syntax for MSSQL. There could be 1 or 100 rows inserted at a time. Rob's answer got my query working. – Matt Busche Feb 09 '12 at 21:41
  • @mrbusche - Interesting it needs parenthesis in 2000. It works right out of the box with 2005. – Leigh Feb 09 '12 at 21:44
  • @Leigh And with SQL 2008 you can use a much more straightforward method - multiple VALUES groups, as a list: http://blog.sqlauthority.com/2008/07/02/sql-server-2008-insert-multiple-records-using-one-insert-statement-use-of-row-constructor/ – Jake Feasel Feb 10 '12 at 01:19
  • @Jake - Cool, I did not realize 2008 supported that syntax. It is one of the things I miss when not using MySQL. – Leigh Feb 10 '12 at 01:38
0

Try naming the columns and putting the unions in a subquery:

INSERT INTO Meeting_RFPMeetings (RFP_ID, Meeting_ID, Inserted, Create_UserID)
SELECT [1], [2], [3], [4]
FROM
(
SELECT 21 AS [1], 15835 AS [2], {ts '2012-02-09 14:08:28'} AS [3], 42126 AS [4]

UNION ALL

SELECT 21 AS [1], 15917 AS [2], {ts '2012-02-09 14:08:28'} AS [3], 42126 AS [4]

UNION ALL

SELECT 21 AS [1], 17052 AS [2], {ts '2012-02-09 14:08:28'} AS [3], 42126 AS [4]
) a
Nick Vaccaro
  • 5,428
  • 6
  • 38
  • 60
  • i tried this and it failed as well. Must be an issue with my MSSQL version. I don't see any reason why this shouldn't work either – Matt Busche Feb 09 '12 at 21:40