How to insert into a derived table, & then select from the final result with Joins. Beginner level.
Hello, I'm trying to add data to a derived table and being new to MS SQL am confused by the correct syntax. Also my google searches are returning 0 results, so I'm not sure if I'm allowed to do what i'm attempting, though I can't see why not. The 2nd stage of this query is to then requery tblSupplierData for additional fields.
INSERT INTO derivedtbl_1 (fldSupplierId, fldItemCode)
SELECT (fldSupplierId, (fldItemCode)
FROM tblSupplierData
WHERE (fldSupplierId = @supplierID) AND (fldIssueSequenceNumber = @sequenceNo)
(SELECT fldSupplierId, fldItemCode
FROM tblSubscriptionSales
WHERE (fldSupplierId = @supplierID) AND (fldSubscriptionId = @subscriptionId) AND (fldCustomerId = @customerID)) As derivedtbl_1
INSERT INTO derivedtbl_1 (fldSupplierId, fldItemCode)
SELECT (fldSupplierId, (fldItemCode)
FROM tblSupplierData
WHERE (fldSupplierId = @supplierID) AND (fldIssueSequenceNumber = @sequenceNo)
(SELECT fldSupplierId, fldItemCode
FROM tblSubscriptionSales
WHERE (fldSupplierId = @supplierID) AND (fldSubscriptionId = @subscriptionId) AND (fldCustomerId = @customerID)) As derivedtbl_1
oops, a mistake - sorry
but as I'm here this page discusses the topic:
http://www.blackwasp.co.uk/SQLSelectInsert.aspx
http://www.blackwasp.co.uk/SQLSelectInsert.aspx
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I get a "Must declare the scalar variable "@supplierID" message when I try this. Any suggestions?
(Oh, and this is after adding ( )) as derivedtbl_1 to the end of the second SELECT. Though the same error appears without as per your original statement.
(Oh, and this is after adding ( )) as derivedtbl_1 to the end of the second SELECT. Though the same error appears without as per your original statement.
there is the presumption that (as you proposed them) that you would still be declaring and using the variables...
declare @supplierID int
declare @sequenceNo int --<< maybe, I don't know what ata type it is
declare@customerID int
declare@subscriptionId int
set @supplierID = 100 --<< whatever values makes sense, do this for each variable
...
then run the query
declare @supplierID int
declare @sequenceNo int --<< maybe, I don't know what ata type it is
declare@customerID int
declare@subscriptionId int
set @supplierID = 100 --<< whatever values makes sense, do this for each variable
...
then run the query
>>after adding ( ))
??
you have a tendency toward overuse of parentheses :)
none of the parentheses that survived into my comment above are actually required
??
you have a tendency toward overuse of parentheses :)
none of the parentheses that survived into my comment above are actually required
ASKER
I'm a beginner to this, so while the answer here didn't work, it sent me down the right path which was exploring the "UNION ALL" command enabling me to solve the problem.
ASKER
PortletPaul, thank you for your time on this question. I am well aware that as a beginner I will be prone to asking and formatting questions in a manner that may come across as counter-intuitive as I get my head around these building blocks. I really appreciate your input.
no problem, you are welcome to keep the question open to arrive at a better result (& this would would be better for me than a C grade :(
please keep in mind that
UNION ALL
has an important difference to
UNION
(without the word 'ALL' a UNION will remove duplicate rows and because of this consumes more processing effort) e.g. see:
http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimal-performance-comparison/
there are times when UNION makes sense, and there are times when UNION ALL is approriate - just be aware of the differences.
please keep in mind that
UNION ALL
has an important difference to
UNION
(without the word 'ALL' a UNION will remove duplicate rows and because of this consumes more processing effort) e.g. see:
http://blog.sqlauthority.com/2009/03/11/sql-server-difference-between-union-vs-union-all-optimal-performance-comparison/
there are times when UNION makes sense, and there are times when UNION ALL is approriate - just be aware of the differences.
ASKER
Oh no, sorry about the C, how can I change it? I'm back after three years and not familiar with the points allocation system. The extra knowledge about UNION processing certainly warrants an upgrade.
cheers, you may request attention and ask to re-open the question.
ASKER