Alter Table automatically - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Alter Table automatically

Author

Topic

junior6202
Starting Member

45 Posts

Posted - 2015-02-19 : 23:35:48

I need to perform this task. say I have a table that the columns are dates.
table months:
[02-2015],[03-2015], [04-2015],[04-2015]....[02-2016].
when the new month comes I want the table to alter the columns automatically instead of manual how we do it now. The new Month table should delete [02-2015] and replace it with [03-2015].

table months:
[03-2015],[04-2015],[05-2015],[06-2015]....[03-2016].

Any help will be appreciated.

Thank you

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-20 : 09:37:57

1. please post your CREATE TABLE statement.

2. Are you saying that the table has a column called [02-2015] that you want renamed to [03-2015]?

3. If you are naming your columns after specific month/year combinations, why? Doesn't look like good design.

pradeepbliss
Starting Member

28 Posts

Posted - 2015-02-23 : 07:42:07

CREATE procedure [dbo].[SP_TestMonthYear]
as
begin
declare @i varchar(max)
declare @j varchar(max)
declare @k varchar(max)
declare @l varchar(max)
declare @M int
set @i = CONVERT(varCHAR(2), getdate(), 101) + '-' + CONVERT(varCHAR(4), getdate(), 120)
set @j= (select top 1 COLUMN_NAME from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='@YourTableName' order by ORDINAL_POSITION desc)
if @i != @j
begin
set @M= (Select top 1 ORDINAL_POSITION from INFORMATION_SCHEMA.COLUMNS where TABLE_NAME='@YourTableName' order by ORDINAL_POSITION desc)
if @M != 1
begin
set @k = 'ALTER TABLE @YourTableName DROP Column ['+@j+']'
exec(@k)
end
set @l = 'ALTER TABLE @YourTableName ADD ['+ @i +'] VARCHAR(max)'
exec(@l)
end
end

Description :
@i -> current month & year like [02-2015]
@j -> last column name from table
@M -> last ordinal column position from table
@k -> to drop column [02-2015] automatically when current_date is 01-03-2015
@l -> to add current month like [03-2015]

gbritton
Master Smack Fu Yak Hacker

2780 Posts

Posted - 2015-02-23 : 13:49:24

I asked for your create table statement not a create procedure statement. Also, you did not answer questions 2 and 3.

Nonetheless, I can see what you are doing. It still looks like a questionable design. e.g. what happens to the data in the column you drop? Do you save it somewhere first? If not, why not?

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources