temp table in dynamic sql - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

temp table in dynamic sql

Author

Topic

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-25 : 09:58:07

Is it possible to do the following in a dynamic sql ?
i.e.

declare @sql nvarchar(max)

set @sql = 'select * into #tblMain from tblMain where name in ('x', 'y', 'z')'

exec sp_executesql @sql

Then I want to run :
select * from @tblMain

It seems I have to use a global table instead i.e. ##tblMain

Any thoughts please?
Thanks

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-25 : 10:00:46

you need to use global temporary table for that.
Why do you need the temporary table in between?

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-25 : 10:14:17

no you don't. AND YOU SHOULD NOT!

if you declare the temp table outside of the dynamic block, you can interact with it.

however, you won't be able to SELECT * INTO it, you have to do a regular INSERT / SELECT

these last few posts really hint at a big underlying problem that you are trying to solve in a really bad way.

What is it that you are *actually* trying to accomplish?

I'm sure there are much better ways than this.

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

Kristen
Test

22859 Posts

Posted - 2012-01-25 : 11:25:22

As T.C. says, it would be best to pre-create the #tblMain table (i.e. using a CREATE TABLE statement, and stick a PK on it whilst you are creating it ...), but for quick & dirty you could do:


declare @sql nvarchar(max)

select * into #tblMain from tblMain WHERE 1=0

set @sql = 'INSERT INTO #tblMain select * from tblMain where name in ('x', 'y', 'z')'

exec sp_executesql @sql

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-25 : 15:55:27

quote:
Originally posted by Kristen

As T.C. says, it would be best to pre-create the #tblMain table (i.e. using a CREATE TABLE statement, and stick a PK on it whilst you are creating it ...), but for quick & dirty you could do:

declare @sql nvarchar(max)

select * into #tblMain from tblMain WHERE 1=0

set @sql = 'INSERT INTO #tblMain select * from tblMain where name in ('x', 'y', 'z')'

exec sp_executesql @sql

Are you sure this works? because I have to create ##tblMain iinstead of #tblMain for this to work ?

Transact Charlie
Master Smack Fu Yak Hacker

3451 Posts

Posted - 2012-01-25 : 16:00:37

yes - it works - the same way that calling a stored proc works with temp tables.

If you create the temp table before calling the dynamic sql you can reference it

example


CREATE TABLE #foo ([var] INT)
INSERT #foo([var]) VALUES(1)

DECLARE @sql NVARCHAR(MAX)

SET @sql = N'SELECT * FROM #foo'

EXEC sp_executeSql @sql

What do you get?

Charlie
===============================================================
Msg 3903, Level 16, State 1, Line 1736
The ROLLBACK TRANSACTION request has no corresponding BEGIN TRANSACTION

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-25 : 16:47:43

quote:
Originally posted by arkiboys

quote:
Originally posted by Kristen

As T.C. says, it would be best to pre-create the #tblMain table (i.e. using a CREATE TABLE statement, and stick a PK on it whilst you are creating it ...), but for quick & dirty you could do:

declare @sql nvarchar(max)

select * into #tblMain from tblMain WHERE 1=0

set @sql = 'INSERT INTO #tblMain select * from tblMain where name in ('x', 'y', 'z')'

exec sp_executesql @sql

Are you sure this works? because I have to create ##tblMain iinstead of #tblMain for this to work ?

It will work as creation of table is happening outside scope of dynamic sql

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

arkiboys
Master Smack Fu Yak Hacker

1433 Posts

Posted - 2012-01-26 : 06:04:55

Thank you all.
Solved.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-01-26 : 09:04:57

quote:
Originally posted by arkiboys

Thank you all.
Solved.

]
Good

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources