how to merge 3 different temp tables data into one

  • mcfarlandparkway

    SSCertifiable

    Points: 7642

    Is there any better way to do this?

     

    create table #student

    (StudentName varchar(20) null,

    StdId int not null,

    Stdgrade varchar(10) null)

    insert into #student

    (StudentName ,

    StdId ,

    Stdgrade )

    select std.Name

    ,std.StdID

    ,dp.stdgrade

    from dbo.student std

    join dbo.department dp

    on std.stdid = dp.id

    where dp.isactive = 1

    insert into #student

    (StudentName ,

    StdId ,

    Stdgrade )

    select std.Name

    ,std.StdID

    ,dp.stdgrade

    from dbo.studentbook std

    join dbo.departmentstore dp

    on std.stdid = dp.id

    where dp.isactive = 2 and sdt.isactive = -1

    insert into #student

    (StudentName ,

    StdId ,

    Stdgrade )

    select std.Name

    ,std.StdID

    ,dp.stdgrade

    from dbo.studentarchive std

    join dbo.departmenthistory dp

    on std.stdid = dp.id

    where dp.isactive = 3 and sdt.isactive = -2

    ----Inserrt all these temp table data into main

    Insert into dbo.StdActiveTable

    (StudentName ,

    StdId ,

    Stdgrade )

    select

    StudentName ,

    StdId ,

    Stdgrade

    from #student

     

     

    problem is when i call the procedure in SSIS package, i am getting error

    The metadata could not be determined because statement uses a temp table..

  • ScottPletcher

    SSC Guru

    Points: 98427

    Insert into dbo.StdActiveTable
    (StudentName ,
    StdId ,
    Stdgrade )

    select std.Name
    ,std.StdID
    ,dp.stdgrade
    from dbo.student std
    join dbo.department dp
    on std.stdid = dp.id
    where (dp.isactive = 1) or
    (dp.isactive = 2 and sdt.isactive = -1) or
    (dp.isactive = 3 and sdt.isactive = -2)

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Jeffrey Williams

    SSC Guru

    Points: 88448

    Reformatting the original query - I was able to reduce your individual steps to this:

     Select std.Name
    , std.StdID
    , dp.stdgrade
    From dbo.student std
    Join dbo.department dp On std.stdid = dp.id
    Where dp.isactive = 1
    Or (
    sdt.isactive = -2
    And dp.isactive In (2, 3)
    );

    Since this can be done in a single query - there is no reason to actually insert this data into a table for later processing.  With that said - if the goal is to have a process that rebuilds the 'main' table on a schedule and outputs that data to another source - then you can add the insert to this...however, I would recommend that you put all of that in a try/catch with an explicit transaction so it can be rolled back in case of failure - that way, if it fails it will roll back to the previous state and the table will not be empty.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • mcfarlandparkway

    SSCertifiable

    Points: 7642

    problem is each select statement is coming from different tables. first select statement is form student table, second one is form studentbook and 3rd is from studentarchive.

     

    all tables has different data but columns selecting is same

  • ScottPletcher

    SSC Guru

    Points: 98427

    Oops.

    You still don't need a temp table.  Instead:

    INSERT INTO ...

    SELECT ...

    UNION

    SELECT ...

    UNION

    SELECT ...

     

    SQL DBA,SQL Server MVP(07, 08, 09) Prosecutor James Blackburn, in closing argument in the Fatal Vision murders trial: "If in the future, you should cry a tear, cry one for them [the murder victims]. If in the future, you should say a prayer, say one for them. And if in the future, you should light a candle, light one for them."

  • Jeffrey Williams

    SSC Guru

    Points: 88448

    mcfarlandparkway wrote:

    problem is each select statement is coming from different tables. first select statement is form student table, second one is form studentbook and 3rd is from studentarchive.

    all tables has different data but columns selecting is same

    I did not catch that - but Scott provided the answer for SQL.  Since this is for SSIS there are other options depending on what you are doing with the data.  For example - you could use a UNION in SSIS to combine the data from 3 separate sources, or a MERGE in SSIS - or separate data flows for each...again, it all depends on the desired end result and where the data is being sent.

    Not saying this should all be done in SSIS - but if this is just a sample of the output and process it could be a better option.  If this is the actual result you need then the UNION in SQL will be the better option.

    Jeffrey Williams
    Problems are opportunities brilliantly disguised as insurmountable obstacles.

    How to post questions to get better answers faster
    Managing Transaction Logs

  • Phil Parkin

    SSC Guru

    Points: 244440

    When you use SSIS to call procs which return data from one or more temp tables, you need to add a WITH RESULT SETS definition to your EXEC PROC call, to tell SSIS about the structure of the data which is being returned.

    If the answer to your question can be found with a brief Google search, please perform the search yourself, rather than expecting one of the SSC members to do it for you.
    See https://www.sqlservercentral.com/articles/forum-etiquette-how-to-post-datacode-on-a-forum-to-get-the-best-help/ for details of how to post T-SQL code-related questions.

Viewing 7 posts - 1 through 7 (of 7 total)

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