March 27, 2002 at 3:29 am
Question :
Is ther a way to create a table using a dynamic name like :
Declare @tablename as varchar(30)
set @tablename = 'TMP_BREAKDOWN_B1'
CREATE TABLE [@tablename](
ID_reason int,
ID_Subreason int,
LNE varchar(2)
)
set @tablename = 'TMP_BREAKDOWN_H1'
CREATE TABLE [@tablename](
ID_reason int,
ID_Subreason int,
LNE varchar(2)
)
I have tried already some things but none of them seem to work.
Does anybody have any suggestions.... All help is welcome on this one
March 27, 2002 at 5:00 am
Try this
DECLARE @SQLString AS VARCHAR(400)
Declare @tablename as varchar(30)
set @tablename = 'TMP_BREAKDOWN_B1'
SET @SQLString = 'CREATE TABLE [' + @tablename + '](
ID_reason int,
ID_Subreason int,
LNE varchar(2)
)'
EXEC(@SQLSTring)
Etc.....
The create table function will not accept a variable as an input for name, however building the string dynamically will and then you execute it runs as though the statement was written in normal format.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
March 27, 2002 at 2:04 pm
THANKS for the info
March 28, 2002 at 5:41 am
Could you explain a bit about why you would do this?
Andy
April 2, 2002 at 1:29 pm
I have several production lines, and I want to use only 1 script. This generates temp tables, which are unique for each line. But using this technique, I only need to maintain 1 script instead of 10 or 15 scripts, which are virtual the same , just different table names.
April 4, 2002 at 12:43 pm
quote:
I have several production lines, and I want to use only 1 script. This generates temp tables, which are unique for each line. But using this technique, I only need to maintain 1 script instead of 10 or 15 scripts, which are virtual the same , just different table names.
In that case, dynamic SQL will not work for you.
Take for example the following query:
exec('create table #t1 (ident int)')
select * from #t1
During exec('<statement>'), the <statement> gets executed in another context so when the exec is done, you will not have a temporary table created as the current context.
select * from #t1 will fail as there is no temporary #t1 in the current user context.
April 4, 2002 at 7:10 pm
You you can still use this technique as long as you plan properly. Try
exec('create table #t1 (ident int)
select * from #t1')
This should work and all run under the same context (sorry I haven't tried it and don't have a machine here to do so). Even if this doesn't work you could do it using ## global temp tables but be carefull not to step on yourself.
"Don't roll your eyes at me. I will tape them in place." (Teacher on Boston Public)
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply