SQL Insert into with multiple subqueries

Question

I am able to get the following code to run in MS Access which is able to add a new row into the "NodeForce" table.

INSERT INTO [NodeForce] (nodeID, mem1, mem1min) 
SELECT '1113', '1752-1', [Row_ID] 
FROM [Element Forces - Frames] WHERE [FrameElem] = '1752-1' 
AND [OutputCase] = 'Case2' AND [ElemStation] = 0

However, when I add one additional subquery such as:

INSERT INTO [NodeForce] (nodeID, mem1, mem1min, mem1max) 
SELECT '1113', '1752-1', [Row_ID] 
FROM [Element Forces - Frames] WHERE [FrameElem] = '1752-1' 
AND [OutputCase] = 'Case2' AND [ElemStation] = 0,
[Row_ID] FROM [Element Forces - Frames] WHERE [FrameElem] = '1752-1' 
AND [OutputCase] = 'Case6' AND [ElemStation] = 12

it produces an error "Number of query values and destination fields are not the same."

How can I insert multiple values using different subqueries?

I also tried:

INSERT INTO ...
VALUES ('1113', '1752-1', (SELECT ... FROM ...), (SELECT ... FROM ...))

which then gave me an error saying "Query input must contain at least one table or query"

Thanks in advance for your help!

yes, the error is right, where is your second query ??? after the comma you have [Row_ID] FROM — Jorge Campos
– Jorge Campos, Commented Nov 14, 2013 at 3:25

Edper · Accepted Answer · 2013-11-14 03:40:12Z

1

Dissecting your query:

INSERT INTO [NodeForce] (nodeID, mem1, mem1min, mem1max) 
SELECT '1113', '1752-1', [Row_ID] 
FROM [Element Forces - Frames] WHERE [FrameElem] = '1752-1' 
AND [OutputCase] = 'Case2' AND [ElemStation] = 0

You are inserting 3 values to 4 columns thus the message "Number of query values and destination fields are not the same."

And the one below has no SELECT and is after the SELECT statement with three values instead of after the [Row_ID] of the first SELECT statement.

,[Row_ID] FROM [Element Forces - Frames] WHERE [FrameElem] = '1752-1' 
AND [OutputCase] = 'Case6' AND [ElemStation] = 12

So, what you need to is to match the number of columns with your values and in that case you need to make the second SELECT to be a subquery that is part of the first query and would only return 1 value to be sure, like:

INSERT INTO [NodeForce] (nodeID, mem1, mem1min, mem1max) 
SELECT '1113', '1752-1', [Row_ID], (SELECT
[Row_ID] FROM [Element Forces - Frames] WHERE [FrameElem] = '1752-1' 
AND [OutputCase] = 'Case6' AND [ElemStation] = 12 LIMIT 1) 
FROM [Element Forces - Frames] WHERE [FrameElem] = '1752-1' 
AND [OutputCase] = 'Case2' AND [ElemStation] = 0

Where:

(SELECT
[Row_ID] FROM [Element Forces - Frames] WHERE [FrameElem] = '1752-1' 
AND [OutputCase] = 'Case6' AND [ElemStation] = 12 LIMIT 1)

is for mem1max which is a subquery that would return only 1 value and is the 4th value to match your number of columns of 4 also.

edited Nov 14, 2013 at 3:40

answered Nov 14, 2013 at 3:28

Edper

9,3621 gold badge32 silver badges49 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BryanT Over a year ago

Great, thank you! I knew I had 4 columns and 3 values, I just was not sure on how to properly add the 4th value.

Edper Over a year ago

@BryanT: Glad that it now work for you. Happy coding and GOD bless!

Collectives™ on Stack Overflow

SQL Insert into with multiple subqueries

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related