How to add columns dynamically in a table????

Question

Post reply

How to add columns dynamically in a table????

talk2riya15

SSC-Addicted

Points: 476
More actions
January 1, 2009 at 9:59 pm

#195549

Hi Everybody,

Can you please tell me how to add columns dynamically in a table...like i have created one employee table with EmployeeId, Name Passpost No,Permanent addresss....i want to add more column dynamically by using store procedure......how can i do this????

Please reply.

Advance Thanks:)

Viewing 4 posts - 1 through 3 (of 3 total)

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

ps. One Orange Chip Points: 29255 More actions · Answer 1

create table #test

(

col1 varchar(100),

col2 varchar(100)

)

create proc addCol @tabname varchar(100),@colName varchar(100), @dataType varchar(100)

AS

declare @sql as varchar(500)

set @sql='alter table ' + @tabname + ' add ' + @colname + ' ' + @dataType

exec @sql

GO

----------------------------------------------

select * from #test

OUTPUT

---------

Col1 COl2

------------------------------

-- dynamically adding one column

exec addcol '#test', 'NewCol','varchar(100)'

select * from #test

OUTPUT

---------

Col1 COl2 NewCol

----------------------------------------------

drop proc addCol

Pradeep Singh

Gail Shaw SSC Guru Points: 1004494 More actions · Answer 2

talk2riya15 (1/1/2009)

i want to add more column dynamically by using store procedure......how can i do this????

Why do you need to do this?

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

Jeff Moden SSC Guru Points: 1000539 More actions · Answer 3

talk2riya15 (1/1/2009)

Hi Everybody,

Can you please tell me how to add columns dynamically in a table...like i have created one employee table with EmployeeId, Name Passpost No,Permanent addresss....i want to add more column dynamically by using store procedure......how can i do this????

Please reply.

Advance Thanks:)

Seriously... same question as what Gail asked... Why do you think you need this? The reason we ask is that folks frequently ask how to come about doing and end result instead of asking how to go about solving the original problem. You, for example, are predisposed in thinking that you want to add columns to a table when something else might be even better.

So, the question remains... why do you think you neecd to do this? 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.
"Change is inevitable... change for the better is not".

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)
Intro to Tally Tables and Functions