can i add column in table variable or temporary table

• mayank jarsaniya

  Hall of Fame

  Points: 3532

  More actions

  June 11, 2009 at 8:25 am

  #155819

  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

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  June 11, 2009 at 8:29 am

  #1008015

  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

• mayank jarsaniya

  Hall of Fame

  Points: 3532

  More actions

  June 11, 2009 at 8:39 am

  #1008030

  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

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  June 11, 2009 at 8:48 am

  #1008039

  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

• Gail Shaw

  SSC Guru

  Points: 1004504

  More actions

  June 11, 2009 at 8:50 am

  #1008043

  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

• spaghettidba

  SSC Guru

  Points: 105732

  More actions

  June 11, 2009 at 8:55 am

  #1008047

  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

• mayank jarsaniya

  Hall of Fame

  Points: 3532

  More actions

  June 12, 2009 at 12:44 am

  #1008409

  Ok.thanks My work is done.

  thanks to you and Gail.