CODEWITHSUNDEEP
sqlsql-serversql-server-2008
Bhanu Chandra
Bhanu Chandra

Reputation: 408

Is there anyway to dynamically add column in a SQL Server table based on current year?

I had a SQL Server table with columns:

target_2011, target_2012, target_2013, target_2014, target_2015

Is there any way to add a dynamic column target_2016 to the table as soon as we enter into the year 2016, based on the current year?

Upvotes: 0

Views: 1371

Answers (1)

Raj
Raj

Reputation: 10853

Create a SQL agent job with

DECLARE @column varchar(20), @SQL varchar(1000)
SET @column = 'target_'+CAST(year(getdate()) AS varchar(4))
SET @SQL = 
'ALTER TABLE <yourtable>
ADD '+@column+' varchar(50)'
EXECUTE (@SQL)

Now schedule this job to run at 00:01 on the 1st of January every year.

While this will do what you are looking for, I agree with the observations in the comment section. This is an inherently bad design and not a good idea at all.

Upvotes: 3

Related Questions