CODEWITHSUNDEEP
sqlms-accessinsert
BryanT
BryanT

Reputation: 13

SQL Insert into with multiple subqueries

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!

Upvotes: 1

Views: 3720

Answers (1)

Edper
Edper

Reputation: 9322

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.

Upvotes: 1

Related Questions