How do I remove the temp table from this query

• Paul DB

  SSC Eights!

  Points: 870

  More actions

  September 9, 2008 at 5:06 pm

  #212422

  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

• Paul DB

  SSC Eights!

  Points: 870

  More actions

  September 9, 2008 at 7:54 pm

  #869369

  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

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736468

  More actions

  September 9, 2008 at 10:00 pm

  #869397

  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.

• Grant Fritchey

  SSC Guru

  Points: 398909

  More actions

  September 10, 2008 at 6:09 am

  #869552

  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

• Paul DB

  SSC Eights!

  Points: 870

  More actions

  September 10, 2008 at 6:12 am

  #869554

  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

• Paul DB

  SSC Eights!

  Points: 870

  More actions

  September 10, 2008 at 6:16 am

  #869557

  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

• Grant Fritchey

  SSC Guru

  Points: 398909

  More actions

  September 10, 2008 at 6:34 am

  #869569

  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

• Paul DB

  SSC Eights!

  Points: 870

  More actions

  September 10, 2008 at 9:49 am

  #869772

  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

• Grant Fritchey

  SSC Guru

  Points: 398909

  More actions

  September 10, 2008 at 11:20 am

  #869834

  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

• Paul DB

  SSC Eights!

  Points: 870

  More actions

  September 11, 2008 at 2:17 pm

  #870630

  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

• Monica Chaturvedi

  SSC Journeyman

  Points: 80

  More actions

  September 14, 2008 at 11:12 pm

  #871591

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

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

• arjun.tewari

  Ten Centuries

  Points: 1013

  More actions

  September 15, 2008 at 12:48 am

  #871600

  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?

• Monica Chaturvedi

  SSC Journeyman

  Points: 80

  More actions

  September 15, 2008 at 1:55 am

  #871616

  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..?

• Paul DB

  SSC Eights!

  Points: 870

  More actions

  September 15, 2008 at 2:42 pm

  #872035

  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

• Lynn Pettis

  SSC Guru

  Points: 442467

  More actions

  September 15, 2008 at 2:57 pm

  #872040

  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

  😎