Dynamic Data Insert

Question

Hi All,

I am trying to insert data into a global temp table from a local temp table dynamically. Since the global temp table need to be appended with a user name towards the end.

Eg: ##List_User1 or ##List_User2

Step 1: Take data from Global Temp table and insert into local temp table

a) Create temp table

b) Insert into temp table

Exec(@InsertGlobalTempSQL_SelectStatement)

Step 2: I am struck here, since local temp table cannot be used into a dynamic sql

I need to refresh the ##List_User1 or User2 table with latest local temp data.

Thanks for your help,

Shreepal

Answer 1

Looks like pretty wrong approach..... 

Sorry cannot test it right now

Exec('create table #tmp (c int);insert into #tmp values (1);'+@InsertGlobalTempSQL_SelectStatement)

---

Best Regards,Uri Dimant SQL Server MVP, http://sqlblog.com/blogs/uri_dimant/

MS SQL optimization: MS SQL Development and Optimization
MS SQL Consulting: Large scale of database and data cleansing
Remote DBA Services: Improves MS SQL Database Performance
SQL Server Integration Services: Business Intelligence

Answer 2

You can use the temp table with dynamic sql after creating the temp table...

this code will not work - 

exec ('create table #temp2 (id int)
     insert #temp2 values(1)')
select * from #temp2

This code will work - 

create table #temp1 (id int)
exec ('insert #temp1 values(1)')

select * from #temp1

---

Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

Answer 3

You can also check the below forum for same type of problem - 

https://social.msdn.microsoft.com/Forums/sqlserver/en-US/dbc4fa1a-55f7-416f-9bfb-c3794260af4b/dynamic-sql-creating-a-temp-table-with-a-name-that-includes-a-random-number?forum=transactsql

---

Please Mark as Answer if my post works for you or Vote as Helpful if it helps you. Kapil Singh

Answer 4

I am trying to insert data into a global temp table from a local temp table dynamically. Since the global temp table need to be appended with a user name towards the end.

Eg: ##List_User1 or ##List_User2

What lead you to make this design decision?

If you tell us the full story of what you are doing, we may be able to help you with a better overall solution.

The problem as described is not that difficult:

CREATE TABLE #temp (...)
EXEC (INSERT #temp(...) SELECT ... FROM ##@globaltemp)

But global temp tables with unknown names sounds like a design mistake anyway.