How to Dynamically alter columns in any table in sql server

This Code will create columns dynamically in temporary table that is #temp.
In this We are finding count of another table(demo_table) as per the requirement whose count vary according to time.

          create table #temp
(
  column1 nvarchar(100) not null

declare @rowcount int=0
declare @ColumnName nvarchar(100)
set @rowcount = (select count(*) from demo_table)
declare @count int =1
while(@rowcount > 1)
      begin
           set @ColumnName = 'column'+convert(nvarchar,@count+1)+''
           exec('alter table #temp
            add '+@ColumnName+' nvarchar(100)')
set @rowcount = @rowcount-1
set @count = @count+1
          end
select * from #temp
drop table #temp

Syntax to alter table is :  ALTER TABLE table_name
                 ADD column_name datatype;

Finally apply select query from temporary table and drop the table as per the circumstances.

Output:
        






Post a Comment

0 Comments