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

Transact-SQL
1

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 the 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

2

What I do:

  1. built a string that will be the statement executed,
  2. substituted any variables into the statement
  3. execute the modified statement

so:
declare @tblname sysname = '##global' declare @stmt nvarchar(max) = N' insert into {target} (col1, col2, ...) select ... ' set @stmt = replace(@stmt, N'{target}', @tblname) sp_executesql @stmt

3

thanks for the reply much appreciated, however when I try and incorporate as below

declare @tblname sysname = '##global'
declare @stmt nvarchar(max) = N'
insert into {target}
select * from @IDs'
print @stmt
set @stmt = replace(@stmt, N'{target}', @tblname)
print @stmt

I get the error

insert into {target}
select * from @IDs
insert into ##global
select * from @IDs
Msg 1087, Level 15, State 2, Line 63
Must declare the table variable "@IDs".

so if I then try

insert into {target}
select * from'+ @IDs+''

I get the same error !

4

Use the same technique for @ids

5

just tried that , I had high hopes but unfortunately same "Must declare the table variable "@ids" error

DECLARE @IDs [ID_udt]

insert @ids values (1)

declare @tblname sysname = '##global'
declare @stmt nvarchar(max) = N'
insert into {target}
select * from {source}'

set @stmt = replace(replace(@stmt, N'{target}', @tblname),N'{source}', '@ids')

print @stmt

execute sp_executesql @stmt

6

FWIW, adapting your code a bit:
`
DECLARE @IDs [ID_udt]

insert @ids values (1)

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

SELECT *
into ##T_Source_74
 from @ids

 SELECT * FROM ##T_Source_74

`
works

7

going back to the O/P this should be fine:

EXEC sp_executesql
N'create table ##T_Source_1234(iid int not null)
INSERT INTO ##T_Source_1234
SELECT ID FROM @IDsXXX',
N'@IDsXXX ID_udt READONLY',
@IDsXXX=@IDs

SELECT * FROM ##T_Source_1234

My approach would be to use this type of test-rig to prove the code and then move the Dynamic SQL to an NVarchar, to create table based on SPID etc.

If I got an error message about @IDs I would change the name (preferably some better than @IDsXXX!) within the internal code - if both are the same it can be harder to know which one the error is referring to.