Insert data in to Temp table from a dynamic query

• suneelyada

  SSC Veteran

  Points: 256

  More actions

  February 21, 2012 at 3:32 am

  #98444

  Hi,

  Is there a way that we can insert data that is returned from dynamic query into a temp table. I cannot create temp table becuase I donot know what are the columns that it is returning.

  I tried something like below but it is not working...

  SELECT * INTO #TempTable FROM ( Dynamic Query)

  I tried CTE but not able to load this into #temptable

  Pls help

  Thanks in advace!!

• sengeet86

  Ten Centuries

  Points: 1252

  More actions

  February 21, 2012 at 3:43 am

  #1449556

  use sp_execute to run dynamic commands.

  java programming

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  February 21, 2012 at 3:48 am

  #1449560

  You need to create the temp table outside of the dynamic query, meaning you need to know or be able to work out the columns beforehand.

  Gail Shaw
  Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
  SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

  We walk in the dark places no others will enter
  We stand on the bridge and no one may pass

• Eric M Russell

  SSC Guru

  Points: 125561

  More actions

  February 22, 2012 at 7:36 am

  #1450287

  GilaMonster (2/21/2012)

  ---

  You need to create the temp table outside of the dynamic query, meaning you need to know or be able to work out the columns beforehand.

  It is possible to select a dynamic SQL statement into a temp table, but all references to the temp table must be executed denamically like so:

  exec('

  select * into #databases from sys.databases;

  select * from #databases;

  ')

  Also, we can execute a stored procedure within OPENROWSET and select the result into a temp table.

  select x.* into #databases

  from openrowset

  (

  'sqlncli',

  'server=(local);trusted_connection=yes;',

  'set fmtonly off; exec sp_databases;'

  ) as x;

  select * from #databases;

  If server=(local) doesn't work, then specify the name of your instance instead. Also, the EXEC statement with OPENROWSET needs to be preceeded by the SET FMTONLY OFF statement.

  "Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

• Paul Paiva

  SSCrazy

  Points: 2237

  More actions

  February 23, 2012 at 9:45 am

  #1450963

  You can use a global temp table, meaning you use double hash marks, ##MyTempTable.

  The table will persist as long as you have a connection (spid) open that is accessing it. This may or may not be a good idea depending on your environment, but it will definitely work.

  - Paul

  DECLARE @s-2 varchar(1000)

  IF object_id('tempdb.dbo.##MyGlobalTemp') Is Not Null

  DROP TABLE ##MyGlobalTemp

  SET @s-2 =

  'SELECT *

  INTO ##MyGlobalTemp

  FROM (select name from sys.databases) as T'

  Exec (@s)

  go

  SELECT *

  FROM ##MyGlobalTemp

  - Paul

  http://paulpaivasql.blogspot.com/