can i add column in table variable or temporary table

  • like

    declare @tbl table(id int identity,index_value_table_name nvarchar(max),index_value_column_name nvarchar(max));

    declare @var nvarchar(max);

    set @var='mynk'

    alter table @tbl

    add column + @var + nvarchar(max);

    select * from @tbl

  • No, you can't with table variables. You can do it with temp tables.

    Take the time to read this[/url] article on the topic. I found it excellent.

    Regards

    Gianluca

    -- Gianluca Sartori

  • I cant I have also tried same with temporary table but i can not

    create table #tbl(id int identity,index_value_table_name nvarchar(max),index_value_column_name nvarchar(max));

    declare @var nvarchar(max);

    set @var='mynk'

    alter table #tbl

    add column + @var + nvarchar(max);

    select * from #tbl

  • The problem is with the variable, you can't use it that way. You should define the whole alter statement in a string variable and the execute it:

    DECLARE @sql nvarchar(4000)

    SET @sql = 'alter table #tbl add ' + @var + ' nvarchar(max); '

    EXEC sp_executesql @sql

    EDITED: removed 'column' keyword. (Damn cut&paste!)

    -- Gianluca Sartori

  • mjarsaniya (6/11/2009)


    I cant I have also tried same with temporary table but i can not

    create table #tbl(id int identity,index_value_table_name nvarchar(max),index_value_column_name nvarchar(max));

    declare @var nvarchar(max);

    set @var='mynk'

    alter table #tbl

    add column + @var + nvarchar(max);

    select * from #tbl

    It's not the temp table that's the problem there. It's that you can't specify a column name in a variable, plus the keyword 'column' doesn't need to be there.

    create table #tbl(id int identity,index_value_table_name nvarchar(max),index_value_column_name nvarchar(max));

    alter table #tbl

    add mynk nvarchar(max);

    select * from #tbl

    Gail Shaw
    Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
    SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

    We walk in the dark places no others will enter
    We stand on the bridge and no one may pass
  • GilaMonster (6/11/2009)


    ... the keyword 'column' doesn't need to be there.

    That's right Gail, I'll edit my previous post.

    Mjarsaniya, you can use the dynamic sql when the columnname is determined at runtime or passed as parameter, otherwise go as Gail suggested.

    -- Gianluca Sartori

  • Ok.thanks My work is done.

    thanks to you and Gail.

Viewing 7 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply