How to create columns automatically in sql server 2008 based on start date and end date

Question

0.00/5 (No votes)

See more:

SQL-Server-2008R2

How to create columns automatically in sql server 2008 based on start date and end date

this is my table

SQL

create table demotable
(
Id int primary key identity(1,1),
Name varchar(50),
StartDate date,
EndDate	date
)

SQL

declare @dates int, @StartDate  datetime, @EndDate datetime
 
---Here, we assume that,
Select   @StartDate  = convert(datetime, '1/7/2015'), @endDate = convert(datetime,'1/10/2015')
 
select @dates = datediff (dd,@StartDate ,@Enddate)
 
 ---Now add columns by dynamically with while loop
 
 While (@dates >0)
begin
   alter table  demotable add [Columnname] datetime
   select @dates  =  @dates - 1
end

if i executes this code a column(columnname) is adding to my sql table with showing a error
*Column names in each table must be unique. Column name 'Columnname' in table 'demotable' is specified more than once.
*

i want to add no of months columns from start date to end date

Posted 23-Jul-15 23:23pm

Member 11382784

Updated 23-Jul-15 23:48pm

Wendelius

v2

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Wendelius · Accepted Answer · 2015-07-23T23:56:00

As I explained in previous answer: How to create new table on every drop down list item select[^] this is most likely a really bad thing to do.

However, if you really need to do this for some reason you must make sure that every column is named differently. So build the ALTER TABLE command dynamically and execute it. Something like

SQL

declare @command nvarchar(max)
declare @dates int, @StartDate  datetime, @EndDate datetime
 
---Here, we assume that,
Select   @StartDate  = convert(datetime, '1/7/2015'), @endDate = convert(datetime,'1/10/2015')
 
select @dates = datediff (dd,@StartDate ,@Enddate)
While (@dates >0) begin
    set @command  = 'alter table demotable add [Columnname' + cast(@dates as nvarchar(max)) + '] datetime'
   exec sp_executesql @command
   select @dates  =  @dates - 1
end

But as said this design will probably cause problems in the future.