How do I remove the temp table from this query

  • I need to not use a temp table in my query (see #TempTable in the code below) because I am going to use this into a .NET TableAdapter.

    THIS SETUP SHOULD HELP YOU HELP ME WITH MY QUESTION 😉

    IF OBJECT_ID('TempDB..#AD','U') IS NOT NULL

    DROP TABLE #AD

    CREATE TABLE #AD

    (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    ControlName VARCHAR(50),

    ControlType VARCHAR(50))

    INSERT INTO #AD

    (ControlName, ControlType)

    SELECT 'cboComplexity','ComboBox' UNION ALL

    SELECT 'cboControlTemplate','ComboBox' UNION ALL

    SELECT 'cboOriginationSite','ComboBox' UNION ALL

    SELECT 'cboProject','ComboBox' UNION ALL

    SELECT 'cboProtocol','ComboBox' UNION ALL

    SELECT 'cboTaskTemplate','ComboBox' UNION ALL

    SELECT 'cboType','ComboBox' UNION ALL

    SELECT 'chkBillforstorage','CheckBox' UNION ALL

    SELECT 'chkGlobalJob','CheckBox' UNION ALL

    SELECT 'chkWebVisible','CheckBox' UNION ALL

    SELECT 'cmuActive','ContextMenu' UNION ALL

    SELECT 'cmuStatus','ContextMenu' UNION ALL

    SELECT 'lblBillingStatus','Label' UNION ALL

    SELECT 'lblComplexity','Label' UNION ALL

    SELECT 'lblControlTemplate','Label' UNION ALL

    SELECT 'lblDescription','Label' UNION ALL

    SELECT 'lblGlobalJob','Label' UNION ALL

    SELECT 'lblNotes','Label' UNION ALL

    SELECT 'lblOrigJob','Label' UNION ALL

    SELECT 'lblOrigSite','Label' UNION ALL

    SELECT 'lblProject','Label' UNION ALL

    SELECT 'lblProtocol','Label' UNION ALL

    SELECT 'lblStudyID','Label' UNION ALL

    SELECT 'lblTaskTemplate','Label' UNION ALL

    SELECT 'lblType','Label' UNION ALL

    SELECT 'mnuActivate','MenuItem' UNION ALL

    SELECT 'mnuApproved','MenuItem' UNION ALL

    SELECT 'mnuCancelled','MenuItem' UNION ALL

    SELECT 'mnuDraft','MenuItem' UNION ALL

    SELECT 'mnuHold','MenuItem' UNION ALL

    SELECT 'mnuInActivate','MenuItem' UNION ALL

    SELECT 'pnlDivider1','Panel' UNION ALL

    SELECT 'pnlDivider2','Panel' UNION ALL

    SELECT 'pnlDivider3','Panel' UNION ALL

    SELECT 'sbpActive','StatusBarPanel' UNION ALL

    SELECT 'sbpMode','StatusBarPanel' UNION ALL

    SELECT 'sbpSite','StatusBarPanel' UNION ALL

    SELECT 'sbpStatus','StatusBarPanel' UNION ALL

    SELECT 'txtBillingStatus','TextBox' UNION ALL

    SELECT 'txtDescription','TextBox' UNION ALL

    SELECT 'txtGlobalJob','TextBox' UNION ALL

    SELECT 'txtNotes','TextBox' UNION ALL

    SELECT 'txtOrigJob','TextBox' UNION ALL

    SELECT 'txtStudyID','TextBox'

    IF OBJECT_ID('TempDB..#AFD','U') IS NOT NULL

    DROP TABLE #AFD

    CREATE TABLE #AFD

    (ID INT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    ControlName VARCHAR(50))

    INSERT INTO #AFD (ControlName)

    SELECT '*DATAACCESSLEVEL' UNION ALL

    SELECT '*SECURITY' UNION ALL

    SELECT 'cboComplexity' UNION ALL

    SELECT 'cboControlTemplate' UNION ALL

    SELECT 'cboOriginationSite' UNION ALL

    SELECT 'cboProject' UNION ALL

    SELECT 'cboProtocol' UNION ALL

    SELECT 'cboTaskTemplate' UNION ALL

    SELECT 'cboType' UNION ALL

    SELECT 'chkGlobalJob' UNION ALL

    SELECT 'chkWebVisible' UNION ALL

    SELECT 'cmuActive' UNION ALL

    SELECT 'cmuStatus' UNION ALL

    SELECT 'mnuActivate' UNION ALL

    SELECT 'mnuApproved' UNION ALL

    SELECT 'mnuDraft' UNION ALL

    SELECT 'mnuInActivate' UNION ALL

    SELECT 'pnlDivider1' UNION ALL

    SELECT 'pnlDivider2' UNION ALL

    SELECT 'pnlDivider3' UNION ALL

    SELECT 'sbpActive' UNION ALL

    SELECT 'sbpMode' UNION ALL

    SELECT 'sbpSite' UNION ALL

    SELECT 'sbpStatus' UNION ALL

    SELECT 'txtBillingStatus' UNION ALL

    SELECT 'txtDescription' UNION ALL

    SELECT 'txtGlobalJob' UNION ALL

    SELECT 'txtNotes' UNION ALL

    SELECT 'txtOrigJob' UNION ALL

    SELECT 'specialControl' UNION ALL

    SELECT 'txtStudyID'

    MY QUERY STARTS HERE

    --===== If the temp table already exists, drop it

    IF OBJECT_ID('TempDB..#TempTable','U') IS NOT NULL

    DROP TABLE #TempTable

    --===== Create the temp table

    CREATE TABLE #TempTable

    (

    IDINT IDENTITY(1,1) PRIMARY KEY CLUSTERED,

    ControlNameVARCHAR(50),

    HasAccessTINYINT,

    ControlTypeVARCHAR(50))

    INSERT INTO #TempTable

    ( ControlName, HasAccess, ControlType)

    SELECT ControlName, 0, ControlType

    FROM #AD

    UNION

    SELECTControlName, 1, NULL

    FROM #AFD

    WHERE (ControlName NOT LIKE '*%')

    SELECTControlName

    ,MAX(HasAccess) AS HasAccess

    ,MAX(ControlType) AS ControlType

    FROM#TempTable

    GROUP BY ControlName

    ORDER BY HasAccess DESC, ControlType, ControlName

    Any ideas?

    Paul DB

  • I understand that this is a long question. Any assistance in explaining the concepts behind a good answer is much appreciated. I'm kinda new at this stuff.

    Paul DB

  • Why not just use the SELECT UNIONS instead of the temp tables in the select? It appears that you're using static values in there.

  • Or, rather than hard coding all those values in a query, create a table and store them there. It'll sure make maintenance easier.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Steve Jones - Editor (9/9/2008)


    Why not just use the SELECT UNIONS instead of the temp tables in the select? It appears that you're using static values in there.

    I did not know that the FROM part accepts a whole other query result (in place of a table or table joins). It amazing what this T-SQL can do. 🙂

    Here's my code.

    SELECT ControlName

    , MAX(HasAccess) AS HasAccess

    , MAX(ControlType) AS ControlType

    FROM

    (

    SELECT ControlName, 0 AS HasAccess, ControlType

    FROM #AD

    UNION

    SELECT ControlName, 1 AS HasAccess, NULL AS ControlType

    FROM #AFD

    WHERE (ControlName NOT LIKE '*%')

    ) AS Controls

    GROUP BY ControlName

    ORDER BY HasAccess DESC, ControlType, ControlName

    Paul DB

  • Grant Fritchey (9/10/2008)


    Or, rather than hard coding all those values in a query, create a table and store them there. It'll sure make maintenance easier.

    Let me clarify something. In my original post, the code under ...

    THIS SETUP SHOULD HELP YOU HELP ME WITH MY QUESTION 😉

    ... is not in my code, but rather something I created for you all to better answer my question.

    My real query is the second code block under...

    MY QUERY STARTS HERE

    Thanks for your suggestion, Grant. 🙂

    Paul DB

  • Paul DB (9/10/2008)


    Steve Jones - Editor (9/9/2008)


    Why not just use the SELECT UNIONS instead of the temp tables in the select? It appears that you're using static values in there.

    I did not know that the FROM part accepts a whole other query result (in place of a table or table joins). It amazing what this T-SQL can do. 🙂

    Here's my code.

    SELECT ControlName

    , MAX(HasAccess) AS HasAccess

    , MAX(ControlType) AS ControlType

    FROM

    (

    SELECT ControlName, 0 AS HasAccess, ControlType

    FROM #AD

    UNION

    SELECT ControlName, 1 AS HasAccess, NULL AS ControlType

    FROM #AFD

    WHERE (ControlName NOT LIKE '*%')

    ) AS Controls

    GROUP BY ControlName

    ORDER BY HasAccess DESC, ControlType, ControlName

    I'm sorry for the misunderstanding.

    Why don't you now go one step further. Replace the #AD with the query that defines #AD

    SELECT...

    FROM (SELECT... FROM xxx) AS a

    UNION...

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey (9/10/2008)


    Why don't you now go one step further. Replace the #AD with the query that defines #AD

    SELECT...

    FROM (SELECT... FROM xxx) AS a

    UNION...

    In my original post, the code block under "THIS SETUP SHOULD HELP YOU HELP ME WITH MY QUESTION ;)" is not part of my query. The purpose of that setup stuff is so you all can create tables that represent the real tables I am referring to in my query. So in my actual query, I do not refer to #AD or #AFD, but rather the real tables that you do not have access to. My team is a little uptight about granting access for all readers of this post to my internal database. 😛

    I could be misinterpreting the reason you suggested to "Replace the #AD with the query that defines #AD"; I figured that you did not know that they were tables. Please let me know if I am reading you wrong.

    I hope I did a better job clarifying this time. Thanks again for your suggestions, Grant. [Smile]

    Paul DB

  • I'll shut up now and go and stand in the corner.

    "The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
    - Theodore Roosevelt

    Author of:
    SQL Server Execution Plans
    SQL Server Query Performance Tuning

  • Grant Fritchey (9/10/2008)


    I'll shut up now and go and stand in the corner.

    I can appreciate your sense of humor. :laugh: I'm sure it makes life more enjoyable for you and everyone you interact with.

    Have a great day.

    Paul DB

  • U can also use sql server 2005's with clause.

    See Sql server 2005's Common Table Expressions for more.

  • Monica Chaturvedi (9/14/2008)


    U can also use sql server 2005's with clause.

    See Sql server 2005's Common Table Expressions for more.

    And how will a CTE help here?

  • Instead of creating a temp table and inserting rows into it, just create a CTE and select values into it. Your temp table is removed now..?

  • Monica Chaturvedi (9/15/2008)


    Instead of creating a temp table and inserting rows into it, just create a CTE and select values into it. Your temp table is removed now..?

    Hi Monica. So that it is clear to all, how would you modify my original query to incorporate your suggestion? Can you please post the updated code? 😀

    Paul DB

  • Using your temp tables from the original post, here is the code you are looking for.

    with Controls (

    ControlName,

    HasAccess,

    ControlType

    ) as (

    select

    ControlName,

    0,

    ControlType

    from

    #AD

    union

    select

    ControlName,

    1,

    null

    from

    #AFD

    where

    ControlName NOT LIKE '*%'

    )

    select

    ControlName,

    max(HasAccess) as HasAccess,

    max(ControlType) as ControlType

    from

    Controls

    group by

    ControlName

    order by

    HasAccess desc,

    ControlType,

    ControlName

    😎

Viewing 15 posts - 1 through 15 (of 21 total)

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