declare @sql varchar(max);

select @sql = 'select * from MyTable'

insert into #tmptable exec(@sql)

select * from #tmptable

quote:

---

Originally posted by Zifter

That is where the problem lies.
I don't know which and how many columns my dynamic sql is going to return, so I don't know how to create the temporary table (or table variable)

---

declare @sql varchar(max)

select @sql = 'select * into #tmptable from MyTable; select * from #tmptable'

exec(@sql)

quote:

---

Originally posted by Zifter

visakh16, if I understand you correctly, you suggest to include the "into #tmptable" in the dynamic sql. Something like this

---

declare @sql varchar(max)

select @sql = 'select * into #tmptable from MyTable; select * from #tmptable'

exec(@sql)

---

Correct? But then the #tmptable is only know within the scope of the dynamic sql. I would prefer to have access to the temporary table outside of the dynamic sql.

The dynamic sql is rather complex and I need to use certain returned values later on.
I *could* try to put all of my logic in one big dynamic sql. But I was hoping to avoid that, improving the readablity...

---

quote:

---

Originally posted by nigelrivett

You can use openrowset or probably a linked server with a select into - but that will create a new connection.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---

quote:

---

if you want it afterwards outside why not create ## tables or permanent tables inside dynamic sql unless you've this executed concurrently by more than 1 sessions

---

quote:

---

Originally posted by nigelrivett

See my previous post.

Other options are
Put all of your code inside a dynamic sql string.

Create the temp table using the format created by the query - could create a temp table in the dynamic sql block and return the format from information_schema then use that to cretae a temp table - would need to create a table then alter it to add the columns dynamically. Then you can insert into it using dynamic sql. All access to the temp table would need to be via dynamic sql as the sp wouldn't have the format.

You're probably getting the idea that this isn't a good way to go.

==========================================
Cursors are useful if you don't know sql.
SSIS can be used in a similar way.
Beer is not cold and it isn't fizzy.

---