x

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

avatar image

Parthasarathy
60 5 7 9

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

avatar image

Kev Riley ♦♦
63.8k 48 61 81

(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:

 EXECUTE('SELECT * INTO ##t FROM someTable')

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

 SELECT * FROM ##t

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

avatar image

Magnus Ahlkvist
20.9k 19 38 42

(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.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x181
x56

asked: Sep 03, 2012 at 06:39 AM

Seen: 1697 times

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

Copyright 2016 Redgate Software. Privacy Policy