• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 337
  • Last Modified:

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
0
StuBabyAight
Asked:
StuBabyAight
  • 6
  • 5
1 Solution
 
StuBabyAightAuthor Commented:
Sorry, I have to go out for two hours and cannot attempt any expert suggestions until I return.
0
 
PortletPaulCommented:
oops, a mistake - sorry
0
 
PortletPaulCommented:
but as I'm here this page discusses the topic:
http://www.blackwasp.co.uk/SQLSelectInsert.aspx
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
PortletPaulCommented:
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:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
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
 
PortletPaulCommented:
cheers, you may request attention and ask to re-open the question.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 6
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now