Inserting rows into a uniquely named global temp table from a UDTT via dynamic SQL

• simon_s

  Ten Centuries

  Points: 1354

  More actions

  June 12, 2017 at 7:12 am

  #337555

  I may well be close to tearing my hair out. I've searched google, tried a thousand variations of the code and I'm typing so hard the keyboard may break. 

  I need to have a global temp table with a unique name that can have values passed into it in from proc A that proc B can then read.

  I have a UDT ;

  CREATE TYPE [ID_udt] AS TABLE(
  ID        int NOT NULL,
  PRIMARY KEY CLUSTERED
(ID ASC)
)

  I create a dynamically named global temp table 

  DECLARE @tablename nvarchar(1000)
set @tablename = ' ##T_Source_' + cast(@@spid as nvarchar(10))
set @sql = 'create table '+@tablename+' ( iid int not null)'
EXEC sp_executesql @statement = @sql

  I

  Declare UDT and try and select from it dynamically (after its been populated )

  DECLARE @IDs [ID_udt]

  insert @ids values (1)

  example A:
  EXEC sp_executesql
N'SELECT ID FROM @IDs',
N'@IDs ID_udt READONLY',
@IDs=@IDs

  All works fine. However if I wish to  do insert the UDT values into the global temp table rather than a select .. whatever I try fails 

  so my question is how can I turn example A into an insert for my freshly created global temp table 

  essentially the end result  needs to be something like

  insert ##T_Source_74
  select * from @ids 

  I really am failing to get my head around it

  many thanks 

  simon

• Sue_H

  SSC Guru

  Points: 90860

  More actions

  June 15, 2017 at 1:53 pm

  #1947021

  simon_s - Monday, June 12, 2017 7:12 AM

  I may well be close to tearing my hair out. I've searched google, tried a thousand variations of the code and I'm typing so hard the keyboard may break. 

  I need to have a global temp table with a unique name that can have values passed into it in from proc A that proc B can then read.

  I have a UDT ;

  CREATE TYPE [ID_udt] AS TABLE(
  ID        int NOT NULL,
  PRIMARY KEY CLUSTERED
(ID ASC)
)

  I create a dynamically named global temp table 

  DECLARE @tablename nvarchar(1000)
set @tablename = ' ##T_Source_' + cast(@@spid as nvarchar(10))
set @sql = 'create table '+@tablename+' ( iid int not null)'
EXEC sp_executesql @statement = @sql

  I

  Declare UDT and try and select from it dynamically (after its been populated )

  DECLARE @IDs [ID_udt]

  insert @ids values (1)

  example A:
  EXEC sp_executesql
N'SELECT ID FROM @IDs',
N'@IDs ID_udt READONLY',
@IDs=@IDs

  All works fine. However if I wish to  do insert the UDT values into the global temp table rather than a select .. whatever I try fails 

  so my question is how can I turn example A into an insert for my freshly created global temp table 

  essentially the end result  needs to be something like

  insert ##T_Source_74
  select * from @ids 

  I really am failing to get my head around it

  many thanks 

  simon

  Maybe I'm missing something but your required end result worked fine for me - also switched to another window, declared @IDs [ID_udt], inserted more values and then inserted those into the temp table using your required end result . Couldn't get any errors so I would guess something in the process is different. Just walking through your example to the end was fine. What errors are you getting?

  Sue