Eliminate #tmp table to single SQL Select statement
in [Programming SQL]
|
From: Jay on 12 Feb 2010 17:14 Wait till you get a load of CTE's and Pivots "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message news:OnzNxoCrKHA.5940(a)TK2MSFTNGP02.phx.gbl... > THANKS to everyone!!! Wow!!! The power of SQL is just amazing. I wish I > had known this awhile ago instead of using temp tables. > > Thanks again. > > > "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message > news:uIyys89qKHA.6064(a)TK2MSFTNGP02.phx.gbl... >>I have been trying to redo some of my SQL without temp tables and Loops so >>here is another question I have not been able to figure out. >> >> Table Bk_Reviews >> Person_ID int >> Book_ID int Book_Comment varchar(2000) >> Person_ID Book_ID Book_Comment >> 100 50 whatever1 >> 100 60 whatever2 >> 105 70 whatever3 >> 105 80 whatever4 << same book >> 105 80 whatever5 << same book >> >> To get the total number of comments by a person I use: >> >> DECLARE @cnt int SELECT @cnt = COUNT(*) FROM Bk_Reviews WHERE Person_ID >> = 105 RETURNS 3 for Person_ID=105 >> >> Most people only comment once for a book but people can have multiple >> comments per book such as Person_ID=105 \ Book_ID=80 >> >> I am currently using a temp table to count the number of Total UNIQUE >> comments excluding duplicates per book. >> >> SELECT DISTINCT Book_ID INTO #tmp_Person2Book >> FROM Bk_Reviews WHERE Person_ID = 105 >> SELECT @cnt = COUNT(*) FROM #tmp_Person2Book RETURNS 2 for Person_ID=105 >> >> Person_ID Book_ID 105 70 105 80 << doesn't >> matter that has two comments >> >> Is there anyway I can do this in a single SELECT statement instead of >> using the temp table tmp_Person2Book? >> Thanks >> >
From: Jennifer Mathews on 13 Feb 2010 14:27 I just did a CTE and it is just tremendous. The code is so much more readable and actually easier to write. Thanks. "Jay" <spam(a)nospam.org> wrote in message news:%23MwyyCDrKHA.6064(a)TK2MSFTNGP02.phx.gbl... > Wait till you get a load of CTE's and Pivots > > "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message > news:OnzNxoCrKHA.5940(a)TK2MSFTNGP02.phx.gbl... >> THANKS to everyone!!! Wow!!! The power of SQL is just amazing. I wish I had known >> this awhile ago instead of using temp tables. >> >> Thanks again. >> >> >> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message >> news:uIyys89qKHA.6064(a)TK2MSFTNGP02.phx.gbl... >>>I have been trying to redo some of my SQL without temp tables and Loops so here is >>>another question I have not been able to figure out. >>> >>> Table Bk_Reviews >>> Person_ID int >>> Book_ID int Book_Comment varchar(2000) >>> Person_ID Book_ID Book_Comment >>> 100 50 whatever1 >>> 100 60 whatever2 >>> 105 70 whatever3 >>> 105 80 whatever4 << same book >>> 105 80 whatever5 << same book >>> >>> To get the total number of comments by a person I use: >>> >>> DECLARE @cnt int SELECT @cnt = COUNT(*) FROM Bk_Reviews WHERE Person_ID = 105 >>> RETURNS 3 for Person_ID=105 >>> >>> Most people only comment once for a book but people can have multiple comments per >>> book such as Person_ID=105 \ Book_ID=80 >>> >>> I am currently using a temp table to count the number of Total UNIQUE comments >>> excluding duplicates per book. >>> >>> SELECT DISTINCT Book_ID INTO #tmp_Person2Book >>> FROM Bk_Reviews WHERE Person_ID = 105 >>> SELECT @cnt = COUNT(*) FROM #tmp_Person2Book RETURNS 2 for Person_ID=105 >>> >>> Person_ID Book_ID 105 70 105 80 << doesn't matter that >>> has two comments >>> >>> Is there anyway I can do this in a single SELECT statement instead of using the temp >>> table tmp_Person2Book? >>> Thanks >>> >> > >
From: Jennifer Mathews on 13 Feb 2010 14:47 But I have a question. Is a CTE just another name for a temp table? Is it creating a physical temp table or it it a memory temp table? "Jay" <spam(a)nospam.org> wrote in message news:%23MwyyCDrKHA.6064(a)TK2MSFTNGP02.phx.gbl... > Wait till you get a load of CTE's and Pivots > > "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message > news:OnzNxoCrKHA.5940(a)TK2MSFTNGP02.phx.gbl... >> THANKS to everyone!!! Wow!!! The power of SQL is just amazing. I wish I had known >> this awhile ago instead of using temp tables. >> >> Thanks again. >> >> >> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message >> news:uIyys89qKHA.6064(a)TK2MSFTNGP02.phx.gbl... >>>I have been trying to redo some of my SQL without temp tables and Loops so here is >>>another question I have not been able to figure out. >>> >>> Table Bk_Reviews >>> Person_ID int >>> Book_ID int Book_Comment varchar(2000) >>> Person_ID Book_ID Book_Comment >>> 100 50 whatever1 >>> 100 60 whatever2 >>> 105 70 whatever3 >>> 105 80 whatever4 << same book >>> 105 80 whatever5 << same book >>> >>> To get the total number of comments by a person I use: >>> >>> DECLARE @cnt int SELECT @cnt = COUNT(*) FROM Bk_Reviews WHERE Person_ID = 105 >>> RETURNS 3 for Person_ID=105 >>> >>> Most people only comment once for a book but people can have multiple comments per >>> book such as Person_ID=105 \ Book_ID=80 >>> >>> I am currently using a temp table to count the number of Total UNIQUE comments >>> excluding duplicates per book. >>> >>> SELECT DISTINCT Book_ID INTO #tmp_Person2Book >>> FROM Bk_Reviews WHERE Person_ID = 105 >>> SELECT @cnt = COUNT(*) FROM #tmp_Person2Book RETURNS 2 for Person_ID=105 >>> >>> Person_ID Book_ID 105 70 105 80 << doesn't matter that >>> has two comments >>> >>> Is there anyway I can do this in a single SELECT statement instead of using the temp >>> table tmp_Person2Book? >>> Thanks >>> >> > >
From: TheSQLGuru on 13 Feb 2010 16:12 Be careful with CTEs. They are sometimes NOT helpful for performance. -- Kevin G. Boles Indicium Resources, Inc. SQL Server MVP kgboles a earthlink dt net "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message news:%23WlVoVOrKHA.3800(a)TK2MSFTNGP06.phx.gbl... > But I have a question. Is a CTE just another name for a temp table? Is > it creating a physical temp table or it it a memory temp table? > > "Jay" <spam(a)nospam.org> wrote in message > news:%23MwyyCDrKHA.6064(a)TK2MSFTNGP02.phx.gbl... >> Wait till you get a load of CTE's and Pivots >> >> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message >> news:OnzNxoCrKHA.5940(a)TK2MSFTNGP02.phx.gbl... >>> THANKS to everyone!!! Wow!!! The power of SQL is just amazing. I wish >>> I had known this awhile ago instead of using temp tables. >>> >>> Thanks again. >>> >>> >>> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message >>> news:uIyys89qKHA.6064(a)TK2MSFTNGP02.phx.gbl... >>>>I have been trying to redo some of my SQL without temp tables and Loops >>>>so here is another question I have not been able to figure out. >>>> >>>> Table Bk_Reviews >>>> Person_ID int >>>> Book_ID int Book_Comment varchar(2000) >>>> Person_ID Book_ID Book_Comment >>>> 100 50 whatever1 >>>> 100 60 whatever2 >>>> 105 70 whatever3 >>>> 105 80 whatever4 << same book >>>> 105 80 whatever5 << same book >>>> >>>> To get the total number of comments by a person I use: >>>> >>>> DECLARE @cnt int SELECT @cnt = COUNT(*) FROM Bk_Reviews WHERE >>>> Person_ID = 105 RETURNS 3 for Person_ID=105 >>>> >>>> Most people only comment once for a book but people can have multiple >>>> comments per book such as Person_ID=105 \ Book_ID=80 >>>> >>>> I am currently using a temp table to count the number of Total UNIQUE >>>> comments excluding duplicates per book. >>>> >>>> SELECT DISTINCT Book_ID INTO #tmp_Person2Book >>>> FROM Bk_Reviews WHERE Person_ID = 105 >>>> SELECT @cnt = COUNT(*) FROM #tmp_Person2Book RETURNS 2 for >>>> Person_ID=105 >>>> >>>> Person_ID Book_ID 105 70 105 80 << doesn't >>>> matter that has two comments >>>> >>>> Is there anyway I can do this in a single SELECT statement instead of >>>> using the temp table tmp_Person2Book? >>>> Thanks >>>> >>> >> >> >
From: Tibor Karaszi on 14 Feb 2010 04:28 It is pretty much like a dynamically defined view. I.e., the query of the CTE and the "outer query" are combined (like a macro) and the whole is optimized. Sometimes it is performs better compared to temp tables, sometimes it performs worse. -- Tibor Karaszi, SQL Server MVP http://www.karaszi.com/sqlserver/default.asp http://sqlblog.com/blogs/tibor_karaszi "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message news:#WlVoVOrKHA.3800(a)TK2MSFTNGP06.phx.gbl... > But I have a question. Is a CTE just another name for a temp table? Is > it creating a physical temp table or it it a memory temp table? > > "Jay" <spam(a)nospam.org> wrote in message > news:%23MwyyCDrKHA.6064(a)TK2MSFTNGP02.phx.gbl... >> Wait till you get a load of CTE's and Pivots >> >> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message >> news:OnzNxoCrKHA.5940(a)TK2MSFTNGP02.phx.gbl... >>> THANKS to everyone!!! Wow!!! The power of SQL is just amazing. I wish >>> I had known this awhile ago instead of using temp tables. >>> >>> Thanks again. >>> >>> >>> "Jennifer Mathews" <waltersjennifer(a)live.com> wrote in message >>> news:uIyys89qKHA.6064(a)TK2MSFTNGP02.phx.gbl... >>>>I have been trying to redo some of my SQL without temp tables and Loops >>>>so here is another question I have not been able to figure out. >>>> >>>> Table Bk_Reviews >>>> Person_ID int >>>> Book_ID int Book_Comment varchar(2000) >>>> Person_ID Book_ID Book_Comment >>>> 100 50 whatever1 >>>> 100 60 whatever2 >>>> 105 70 whatever3 >>>> 105 80 whatever4 << same book >>>> 105 80 whatever5 << same book >>>> >>>> To get the total number of comments by a person I use: >>>> >>>> DECLARE @cnt int SELECT @cnt = COUNT(*) FROM Bk_Reviews WHERE >>>> Person_ID = 105 RETURNS 3 for Person_ID=105 >>>> >>>> Most people only comment once for a book but people can have multiple >>>> comments per book such as Person_ID=105 \ Book_ID=80 >>>> >>>> I am currently using a temp table to count the number of Total UNIQUE >>>> comments excluding duplicates per book. >>>> >>>> SELECT DISTINCT Book_ID INTO #tmp_Person2Book >>>> FROM Bk_Reviews WHERE Person_ID = 105 >>>> SELECT @cnt = COUNT(*) FROM #tmp_Person2Book RETURNS 2 for >>>> Person_ID=105 >>>> >>>> Person_ID Book_ID 105 70 105 80 << doesn't >>>> matter that has two comments >>>> >>>> Is there anyway I can do this in a single SELECT statement instead of >>>> using the temp table tmp_Person2Book? >>>> Thanks >>>> >>> >> >> >
First
|
Prev
|
Next
|
Last
Pages: 1 2 3 Prev: SSRS EXCEL Datasource Next: Help with sort on Recursive CTE |