Using Temp Tables/multiple select in source table

  • HI all,

    I hope someone can help me with this one.  I know I have done something like this in the past but for the life of me can not figure out how to get around it.

    I have a stored proc that pulls together some data into a temp table and then uses that temp table when selecting out as the source data for my DTS transformation.  Unfortunately the DTS designer will not see the fields from the lower select and allow me to map them to the destination fields in the destimation table.

    I've tried creating a global temp table, a permanent table, and setting Set No Count On but nothing seems to work.  I can preview the data.

    I am really hoping the answer is not to script each transformation in Avtivex!

    Example:

    Create Procedure as

    Select x1, x2 into #tbl1 from permtable1 x

    select t.1, t.2, y.3 from #tbl1 t, permtable1 y

    go

    I want the second select to end up in the destination.

    Sorry this seems so basic but I just can't seem to figure out a way around it.

    Beth

  • PEBCAK!  User error here, please disregard!

  • Without knowing a bit more about your data and why you need to create the temp table first it's somewhat difficult to help but here goes anyhow...

    Why not create a veiw that you can join in your select statement?  Also have you tried joing the table to itself? Or using a subquery in your from line ie..

    SELECT temptbl.Pub_id, y.pub_name

    FROM publishers y, (SELECT pub_name, pub_id FROM publishers WHERE country <> 'USA') AS temptbl

    This creates a cartision product, because there is no WHERE statement defining the join.  With better knowledge of your enviroment that can't be done. 

    Anyhow give it a go and let me know how you come out.

    To help us help you read this[/url]For better help with performance problems please read this[/url]

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply