Creating dynamic table in SP

Hi all,
I have to create a dynamic table in a stored procedure, where it's name is
dynamic
I tried the following syntax:
select @.sql = 'create table #' + @.tbl_nm + ' (' + @.tbl_columns + ')'
execute (@.sql)
select @.sql = 'insert into ' + @.tbl_nm + ' select * from aaa_tbl '
execute(@.sql)
It said table not found when I tried to insert data to the newly created
table.
It seems the temporary table can be successfully created, but it is not
within the scope of the stored procedure and therefore, I cannot access the
table created using the above the syntax.
How could I solved this problem?
Thanks in advance.
--
Regards,
JoannaJoanna,
Are you really sure you need to use dynamic SQL here? If you're doing
'select * from aaa_tbl', then surely you know how to define your temporary
table, because it matches aaa_tbl? (I'm sure it's more complicated than
this, but I'd like to know why)
You're creating the temporary table within the context of the execute
statement, not within the context of the stored procedure itself.
Rob
"Joanna" <joanna@.abc.com> wrote in message
news:%23TDXEZRiGHA.1208@.TK2MSFTNGP02.phx.gbl...
> Hi all,
> I have to create a dynamic table in a stored procedure, where it's name is
> dynamic
> I tried the following syntax:
> select @.sql = 'create table #' + @.tbl_nm + ' (' + @.tbl_columns + ')'
> execute (@.sql)
> select @.sql = 'insert into ' + @.tbl_nm + ' select * from aaa_tbl '
> execute(@.sql)
> It said table not found when I tried to insert data to the newly created
> table.
> It seems the temporary table can be successfully created, but it is not
> within the scope of the stored procedure and therefore, I cannot access
> the table created using the above the syntax.
> How could I solved this problem?
> Thanks in advance.
> --
> Regards,
> Joanna
>|||Hi Rob,

> Are you really sure you need to use dynamic SQL here? If you're doing
> 'select * from aaa_tbl', then surely you know how to define your temporary
> table, because it matches aaa_tbl? (I'm sure it's more complicated than
> this, but I'd like to know why)
It is because I want to have the table structure of the temporary table
exactly the same as aaa_tbl.
I have a SP to get the table structure of aaa_tbl and therefore, when the
table structure of aaa_tbl changed, I don't have to modify any code.
Also, the table name of the newly created table is dynamic and therefore, I
have to use dynamic SQL.
--
Regards,
Joanna
"Rob Farley" <rob_farley@.hotmail.com> glsD:OK%23HbdRiGHA.4276@.TK2MSFTNGP03.phx.gb
l...
> Joanna,
> Are you really sure you need to use dynamic SQL here? If you're doing
> 'select * from aaa_tbl', then surely you know how to define your temporary
> table, because it matches aaa_tbl? (I'm sure it's more complicated than
> this, but I'd like to know why)
> You're creating the temporary table within the context of the execute
> statement, not within the context of the stored procedure itself.
> Rob
>
> "Joanna" <joanna@.abc.com> wrote in message
> news:%23TDXEZRiGHA.1208@.TK2MSFTNGP02.phx.gbl...
>|||Joanna
An option would be execute a SELECT INTO statement as the structure of the
new table created by the SELECT INTO is defined by the attributes of the
expressions in the select list.
eg.
DECLARE @.tbl_nm SYSNAME
SET @.tbl_nm = 'foo'
EXEC('SELECT * INTO ' + @.tbl_nm + ' FROM aaa_tbl')
All the usual caveats apply to dynamic SQL such as SQL Injectsion etc. as
you do not want a user to be able to pass the table name in as a parameter
and instead pass in something like SET @.tbl_nm = 'foo from sysobjects; drop
database northwind; --'
HTH
- Peter Ward
WARDY IT Solutions
"Joanna" wrote:

> Hi all,
> I have to create a dynamic table in a stored procedure, where it's name is
> dynamic
> I tried the following syntax:
> select @.sql = 'create table #' + @.tbl_nm + ' (' + @.tbl_columns + ')'
> execute (@.sql)
> select @.sql = 'insert into ' + @.tbl_nm + ' select * from aaa_tbl '
> execute(@.sql)
> It said table not found when I tried to insert data to the newly created
> table.
> It seems the temporary table can be successfully created, but it is not
> within the scope of the stored procedure and therefore, I cannot access th
e
> table created using the above the syntax.
> How could I solved this problem?
> Thanks in advance.
> --
> Regards,
> Joanna
>
>|||Ok, it does sound like you really do want dynamic SQL here.
How are you going to query your table, if everything about it is dynamic?
And if you only want to query certain known columns, then perhaps you should
only query certain known columns in the first place?
"Joanna" <joanna@.abc.com> wrote in message
news:ez1C36RiGHA.1600@.TK2MSFTNGP04.phx.gbl...
> Hi Rob,
>
> It is because I want to have the table structure of the temporary table
> exactly the same as aaa_tbl.
> I have a SP to get the table structure of aaa_tbl and therefore, when the
> table structure of aaa_tbl changed, I don't have to modify any code.
> Also, the table name of the newly created table is dynamic and therefore,
> I have to use dynamic SQL.
> --
> Regards,
> Joanna
> "Rob Farley" <rob_farley@.hotmail.com>
> glsD:OK%23HbdRiGHA.4276@.TK2MSFTNGP03.phx.gbl...
>|||Joanna,
How about
select * into #aaa_tbl
from aaa_tbl
where 1 = 0
-- Steve Kass
-- Drew University
-- http://www.stevekass.com
Joanna wrote:

>Hi Rob,
>
>
>It is because I want to have the table structure of the temporary table
>exactly the same as aaa_tbl.
>I have a SP to get the table structure of aaa_tbl and therefore, when the
>table structure of aaa_tbl changed, I don't have to modify any code.
>Also, the table name of the newly created table is dynamic and therefore, I
>have to use dynamic SQL.
>|||Probably won't help her Steve, as aaa_tbl might not be the name of the
table.
"Steve Kass" <skass@.drew.edu> wrote in message
news:%23RqQNmSiGHA.3440@.TK2MSFTNGP02.phx.gbl...
> Joanna,
> How about
> select * into #aaa_tbl
> from aaa_tbl
> where 1 = 0
> -- Steve Kass
> -- Drew University
> -- http://www.stevekass.com
> Joanna wrote:
>|||Joanna (joanna@.abc.com) writes:
> It is because I want to have the table structure of the temporary table
> exactly the same as aaa_tbl.
> I have a SP to get the table structure of aaa_tbl and therefore, when the
> table structure of aaa_tbl changed, I don't have to modify any code.
> Also, the table name of the newly created table is dynamic and
> therefore, I have to use dynamic SQL.
What are you real business requirements?
A database is supposed to have a stable schema. Table may be changed
with application upgrades, but when the system is live, tables should
not be created, dropped or altered.
While the solution for your problem as stated may be to use a global
temp table with two ##, it would mainly help to reinforce the impression
that you have painted yourself into the wrong corner. Creating tables
dynamically, reading its schema etc is not aimed to get the best performance
out of the system.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx