Union with temp table

  • mcfarlandparkway

    SSCertifiable

    Points: 7645

    insert into dbo.details

    (

    DetailId

    ,DetailName

    )

    select distinct

    st.stdid

    ,st.name

    from dbo.student st

    UNION

    select distinct

    dt.depid

    ,dt.name

    from dbo.department dt

    UNION

    select distinct

    Ot.Ordid

    ,Ot.OrderName

    from dbo.Orders Ot

    i have 3 different select statements where i am doing union and inserting into table; How can i keep all the three select statements in a temp table and use temp table inserting into main.

    while inserting into main table to avoid duplicates data which already exists in main table we should not insert again from temp table.

  • Mr. Brian Gale

    SSC-Insane

    Points: 23165

    To insert it into a temp table, just insert it into a temp table instead of into dbo.details.  once that is done, to put the values from the temp table into details excluding duplicates, just have a where clause on your insert.

    something like:

    CREATE TABLE #tmpTable (DetailID INT, DetailName VARCHAR(MAX))
    insert into #tmpTable (DetailID, DetailName)
    <your code from above>
    INSERT INTO dbo.details (DetailID, DetailName)
    SELECT DetailID, DetailName FROM #tmpTable
    WHERE #tmpTable.DetailID not in (select detailID from dbo.details)

    With your code, I would recommend removing the "DISTINCT" as it is not needed.  It is already going to be a distinct list.

  • drew.allen

    SSC Guru

    Points: 76739

    Why are you cramming Students, Departments, and Orders into the same table without any way to differentiate them?  They're completely different objects with completely different details.

    Drew

    J. Drew Allen
    Business Intelligence Analyst
    Philadelphia, PA

  • Sumathi

    SSC Rookie

    Points: 28

    I dont think there are duplicates in your result set. However, You can use the below:

    Select A.* Into #temp From (

    <<Your code>>

    ) As A Where A.DetailId Not In(Select DetailId From dbo.details)

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

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