Use #temp inside a parenthesis

Question

How use a #temp inside a parenthesis?

Or how to use a table variable inside a parenthesis?

Clearly the real query is much more complex.

Fails:

IF OBJECT_ID(N'tempdb..#Temp', N'U') IS NOT NULL DROP TABLE #Temp 
CREATE TABLE #Temp (sID int NOT NULL);
select count(*) 
from 
(
  insert into #Temp 
  select top 10 sID from [docSVsys]  
  select * from #temp
) as [count] 
IF OBJECT_ID(N'tempdb..#Temp', N'U') IS NOT NULL DROP TABLE #Temp

Fails:

select count(*) 
from 
(
  IF OBJECT_ID(N'tempdb..#Temp', N'U') IS NOT NULL DROP TABLE #Temp 
  CREATE TABLE #Temp (sID int NOT NULL);
  insert into #Temp 
  select top 10 sID from [docSVsys]  
  select * from #temp
) as [count] 
IF OBJECT_ID(N'tempdb..#Temp', N'U') IS NOT NULL DROP TABLE #Temp

Works:
But I need to use the #temp inside a parenthesis

IF OBJECT_ID(N'tempdb..#Temp', N'U') IS NOT NULL DROP TABLE #Temp 
CREATE TABLE #Temp (sID int NOT NULL);
insert into #Temp 
select top 10 sID from [docSVsys]  
select * from #temp
IF OBJECT_ID(N'tempdb..#Temp', N'U') IS NOT NULL DROP TABLE #Temp

If you just need to know how many rows were `insert`ed, what's wrong with `@@ROWCOUNT`? — Damien_The_Unbeliever, Jun 14 '13 at 13:35
@Damien_The_Unbeliever From the question: "Clearly the real query is much more complex." — paparazzo, Jun 14 '13 at 13:37
Just noticed your "the real query is much more complex" which may make my previous comment off target. But the issue really is, what are you trying to do? It's never a requirement to "put X in parenthesis" - you've not told us what your actual requirement is. All you've told us is that your perceived *solution* to a problem is "put X in parenthesis". — Damien_The_Unbeliever, Jun 14 '13 at 13:39
@Damien_The_Unbeliever Thanks, but, looking for an answer to the stated question. — paparazzo, Jun 14 '13 at 13:47
You might want to check [What is the XY Problem?](http://meta.stackexchange.com/questions/66377/what-is-the-xy-problem). — Damien_The_Unbeliever, Jun 14 '13 at 13:52
If all you are doing is what is in the accepted answer then just do `select COUNT(*) from #temp`. — Martin Smith, Jun 14 '13 at 15:04
@MartinSmith As stated on line 3 of the question. "Clearly the real query is much more complex." — paparazzo, Jun 14 '13 at 15:47

Alexander Sigachov · Accepted Answer · 2013-06-14T14:15:17.103

1

You can use only SELECT statements in parenthesis (subquery). But you can not use INSERT statement here (#-table does not matter). Try

IF OBJECT_ID(N'tempdb..#Temp', N'U') IS NOT NULL DROP TABLE #Temp 
CREATE TABLE #Temp (sID int NOT NULL);

insert into #Temp 
select top 10 sID from [docSVsys];

select count(*) 
from 
(
  select * from #temp
) as [count] 
IF OBJECT_ID(N'tempdb..#Temp', N'U') IS NOT NULL DROP TABLE #Temp

Or use OUTPUT clause for complex case

edited Jun 14 '13 at 14:15

answered Jun 14 '13 at 14:06

Alexander Sigachov

1,541
11
16

Thanks worked. Don't think output is I need for this. Populating that #temp is expensive and then it is reused in some joins and unions to get "results". – paparazzo Jun 14 '13 at 14:38

Use #temp inside a parenthesis

1 Answers1