creating tables using a variable

Hi ,
i actually wanted to create a table with values from a
variable so as not to overwrite the same table in the same
database
i have done the followings
1 --> declare @.tblname as char(100)
2 -->set @.tblname = 'tbl123' + convert(char(10) , getdate
() , 112)
3 --> select * into @.tblname from xyz
but when i parse these satement , it failed at step 3
citing syntax error
how do i actually create a table with name from a
variable ?
thks & rdgs
Hi,
You have to use dynamic sql execution. See the below piece of code.
declare @.tblname char(100)
declare @.sql nvarchar(200)
set @.tblname = 'tbl123' + convert(char(10) , getdate() , 112)
set @.sql='select * into '+@.tblname +' from sysusers'
exec sp_executesql @.sql
exec ('select * from '+@.tblname)
Thanks
Hari
MCDBA
"maxzsim" wrote:

> Hi ,
> i actually wanted to create a table with values from a
> variable so as not to overwrite the same table in the same
> database
> i have done the followings
> 1 --> declare @.tblname as char(100)
> 2 -->set @.tblname = 'tbl123' + convert(char(10) , getdate
> () , 112)
> 3 --> select * into @.tblname from xyz
> but when i parse these satement , it failed at step 3
> citing syntax error
> how do i actually create a table with name from a
> variable ?
> thks & rdgs
>
|||thks Hari =)
>--Original Message--
>Hi,
>You have to use dynamic sql execution. See the below
piece of code.
>declare @.tblname char(100)
>declare @.sql nvarchar(200)
>set @.tblname = 'tbl123' + convert(char(10) , getdate() ,
112)[vbcol=seagreen]
>set @.sql='select * into '+@.tblname +' from sysusers'
>exec sp_executesql @.sql
>exec ('select * from '+@.tblname)
>Thanks
>Hari
>MCDBA
>"maxzsim" wrote:
a[vbcol=seagreen]
same[vbcol=seagreen]
getdate
>.
>

creating tables using a variable

Hi ,
i actually wanted to create a table with values from a
variable so as not to overwrite the same table in the same
database
i have done the followings
1 --> declare @.tblname as char(100)
2 -->set @.tblname = 'tbl123' + convert(char(10) , getdate
() , 112)
3 --> select * into @.tblname from xyz
but when i parse these satement , it failed at step 3
citing syntax error
how do i actually create a table with name from a
variable ?
thks & rdgsHi,
You have to use dynamic sql execution. See the below piece of code.
declare @.tblname char(100)
declare @.sql nvarchar(200)
set @.tblname = 'tbl123' + convert(char(10) , getdate() , 112)
set @.sql='select * into '+@.tblname +' from sysusers'
exec sp_executesql @.sql
exec ('select * from '+@.tblname)
Thanks
Hari
MCDBA
"maxzsim" wrote:
> Hi ,
> i actually wanted to create a table with values from a
> variable so as not to overwrite the same table in the same
> database
> i have done the followings
> 1 --> declare @.tblname as char(100)
> 2 -->set @.tblname = 'tbl123' + convert(char(10) , getdate
> () , 112)
> 3 --> select * into @.tblname from xyz
> but when i parse these satement , it failed at step 3
> citing syntax error
> how do i actually create a table with name from a
> variable ?
> thks & rdgs
>|||thks Hari =)
>--Original Message--
>Hi,
>You have to use dynamic sql execution. See the below
piece of code.
>declare @.tblname char(100)
>declare @.sql nvarchar(200)
>set @.tblname = 'tbl123' + convert(char(10) , getdate() ,
112)
>set @.sql='select * into '+@.tblname +' from sysusers'
>exec sp_executesql @.sql
>exec ('select * from '+@.tblname)
>Thanks
>Hari
>MCDBA
>"maxzsim" wrote:
>> Hi ,
>> i actually wanted to create a table with values from
a
>> variable so as not to overwrite the same table in the
same
>> database
>> i have done the followings
>> 1 --> declare @.tblname as char(100)
>> 2 -->set @.tblname = 'tbl123' + convert(char(10) ,
getdate
>> () , 112)
>> 3 --> select * into @.tblname from xyz
>> but when i parse these satement , it failed at step 3
>> citing syntax error
>> how do i actually create a table with name from a
>> variable ?
>> thks & rdgs
>.
>sql

Creating table by variable name

Hi
How can i Create a table with variable name
eg
suppose variable x=tab1
I want the table created to be tab1 if x=tab2 then table created shd be tab2.. so how can this be dynamically done(creation of tables as well as stored procs)

i tried : create table @.x .. it doesnt seem to work
please help
regardssomething like...

CREATE PROCEDURE [dbo].[usp_CreateTable]

@.tablename varchar(200)

AS

sp_executesql('create table ' + @.tablename + ' column definations etc ')|||thanks.. Ill try this out :-)

Creating Dynamic Variables

Is there any way I can create variables dynamically in SQL server procedures?

e.g., I initially declare variable
@.col1='1'
@.col2='0'

and in a for loop with variable @.i,

I have @.str='@.col'+@.i
if @.i=1 then the @.str='@.col1'
and try to insert the value this variable holds into a table as

insert into table1 values(@.str)

Here I want to insert the value of @.col1(which is 1) and NOT '@.col1'

I tried so many time unsuccessfully.Has anyone done this kind of stuff before?any work arounds for this?

appreciate u'r help..

Thanks!Chances are you can accomplish your objectives using standard SQL, with maybe a little help from temporary tables, table variables, or case statements. I don't understand what you are trying to do, but your code looks like SQL written by a VB programmer (loops, inserting parameters, etc...). These are legitimate tools for SQL, but most procedures don't require them.

Using a dynamic SQL statement may be an option, either to assign the value to @.str or for the insert statement, but dynamic SQL is tricky because it executes in a distinct environment and your variables go out of scope.

If you could briefly describe your application, relevant table structures, and the task you are trying to perform, I may be able to give your some programming algorythms that are more appropriate for SQL.

blindman