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
StuBabyAightAsked:
Who is Participating?
 
PortletPaulConnect With a Mentor freelancerCommented:
and this should work I believe- with a union all
SELECT
      fldSupplierId
    , fldItemCode
INTO derivedtbl_1
FROM tblSupplierData
WHERE (fldSupplierId = @supplierID)
    AND (fldIssueSequenceNumber = @sequenceNo)

UNION ALL

SELECT
      fldSupplierId
    , fldItemCode
FROM tblSubscriptionSales
WHERE (fldSupplierId = @supplierID)
    AND (fldSubscriptionId = @subscriptionId)
    AND (fldCustomerId = @customerID)

Open in new window

0
 
StuBabyAightAuthor Commented:
Sorry, I have to go out for two hours and cannot attempt any expert suggestions until I return.
0
 
PortletPaulfreelancerCommented:
oops, a mistake - sorry
0
Cloud Class® Course: MCSA MCSE Windows Server 2012

This course teaches how to install and configure Windows Server 2012 R2.  It is the first step on your path to becoming a Microsoft Certified Solutions Expert (MCSE).

 
PortletPaulfreelancerCommented:
but as I'm here this page discusses the topic:
http://www.blackwasp.co.uk/SQLSelectInsert.aspx
0
 
StuBabyAightAuthor Commented:
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.
0
 
PortletPaulfreelancerCommented:
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

>>after adding ( ))
??

you have a tendency toward overuse of parentheses :)
none of the parentheses that survived into my comment above are actually required
0
 
StuBabyAightAuthor Commented:
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.
0
 
StuBabyAightAuthor Commented:
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.
0
 
PortletPaulfreelancerCommented:
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.
0
 
StuBabyAightAuthor Commented:
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.
0
 
PortletPaulfreelancerCommented:
cheers, you may request attention and ask to re-open the question.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.