creating temporary table

ASkEPA
ASkEPA used Ask the Experts™
on
Hello Experts.

Does anyone have an idea how to get around this error?
I would add that it applied ##urlopy2 but this causes other errors so I looking for another solution.
...
set @strCreate = 'create table #urlopy2(' + @cols + ')'
Exec sp_executesql @strCreate
select * from #urlopy2

Msg 208, Level 16, State 0, Procedure sp_poza_biurem6, Line 89
Invalid object name '#urlopy2'.

Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
use this one

declare @cols varchar(max)
declare @strCreate nvarchar(max)
set @cols=' id int, name varchar(10) '
set @strCreate = 'create table ##urlopy2(' + @cols + ')'
Exec sp_executesql @strCreate
select * from ##urlopy2
drop table ##urlopy2

Open in new window

you are making local temp table which is going beyond scope and you are not able to access it so you may have to use global temp table.
try this:
select SomeFields into #urlopy2  from SomeTable

select * from #urlopy2

Open in new window

11/26 Forrester Webinar: Savings for Enterprise

How can your organization benefit from savings just by replacing your legacy backup solutions with Acronis' #CyberProtection? Join Forrester's Joe Branca and Ryan Davis from Acronis live as they explain how you can too.

Commented:
if it is table type variable use this code
declare @table1 as table(col1 int,col2 varchar(10))
insert into @table1 values(1,      'sample1')
insert into @table1 values(2    ,  'sample2')

select *   from @table1 

Open in new window

using the belo code will create a temp table called #urlopy2 with the following columns:

ProductID,
Added,
UserName

it creates the column with the same datatype as the source

obvioustly you can add complex from / group / having / where etc....

just remember to delte the temp table after you are done ... this does not happen auto-mo-tatically ;)
select ProductID, Added, UserName into #urlopy2  from Cart 

Open in new window

@       ASkEPA,,

I am disagree with the answer you have selected. I was the first who solved your problem and saw you what was the problem in your code, same thing is written there on that link.

Commented:
you can also  create temp table instead of table type variable.. but it's not suggestible for performance reasons
create table #Temp2
(
 result1 varchar(50)
)
delete #Temp2

drop table #Temp2

Open in new window

ASkEPA wanted temporary table to be created within dynamic SQL that was he wanted and that was his question otherwise there is not issue if he wanted to have simple temp table.

Author

Commented:
This is the solution I needed. thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial