Get results into table from dynamic tsql

How can we insert the dynamic query result into temporary table without defined the structure of that temporary table.
more ▼

asked Sep 03, 2012 at 06:39 AM in Default

Parthasarathy gravatar image

60 4 6 8

I tried Something like that: insert into @table EXEC(@sql)

Without define the @table..how can solve this problem
Sep 04, 2012 at 05:25 AM Parthasarathy
See my answer. But please be aware of the global temporary table concurrency issues that I mention as well.
Sep 04, 2012 at 06:23 AM Magnus Ahlkvist
(comments are locked)
10|1200 characters needed characters left

2 answers: sort voted first

To generate a temporary table dynamically without specificng the structure up front, use the SELECT INTO syntax

select col1, col2, col3
into #YourTempTable
from AnotherTable
The datatypes will be determined by the base datatypes in the source tables (or expressions if an explicit convert is done)
more ▼

answered Sep 03, 2012 at 07:29 AM

Kev Riley gravatar image

Kev Riley ♦♦
53.8k 47 49 76

(comments are locked)
10|1200 characters needed characters left

When you say "dynamic query results", I assume you mean the results from a string containing sql, that you execute with EXECUTE('select * from someTable') If that's the case, you have only one option that I can think of: Use a global temporary table.

Like this:


Then you can use the global temporary table in your code. Like this:

The problem with this solution is that the temporary table you create is global. It will cause problems in a multi-user environment, if this is run by two users at the (more or less) same time the SELECT INTO will fail since the table already exists.
more ▼

answered Sep 03, 2012 at 11:12 AM

Magnus Ahlkvist gravatar image

Magnus Ahlkvist
16.6k 17 20 33

(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Sep 03, 2012 at 06:39 AM

Seen: 1310 times

Last Updated: Sep 04, 2012 at 06:23 AM