The Columns cannot be created in sql server 2005

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

Hi,

I have a table with 175 columns.i need to create more columns in the same table.
It was created but removed once i debug the application.

Can any one tell me the issue?
How can i solve this and creating more columns?

Posted 6-May-12 20:10pm

Thendral.N

Add a Solution

Comments

Sandeep Mewara 7-May-12 2:13am

It was created but removed once i debug the application.
Not clear.

Further, having 175 columns in a particular table - sounds bad design.

Thendral.N 7-May-12 3:25am

the table is having 175 columns.i need to create more columns in the table.If i modify the table the column was created.once i close the table and open means the extra columns are removed.

walterhevedeich 9-May-12 3:25am

I agree with Sandeep, 175 columns in a table is a bad design, and you might want to rethink it, before it will get worse. Of course, we can point you out some things that might solve your error, but we might just be leading you to wrong direction by answering your question. So I guess the solution is not to solve your error, but to re design your database.

Thendral.N 7-May-12 4:00am

i got this error,

Table 'merchantlists' has changed:
- Column 'updateddate26' was deleted and will be recreated without data.
- Column 'MPN26' was deleted and will be recreated without data.
- Column 'Merchant26' was deleted and will be recreated without data.
- Column 'Baseprice26' was deleted and will be recreated without data.
- Column 'Shipping26' was deleted and will be recreated without data.
- Column 'Tax26' was deleted and will be recreated without data.
- Column 'BLPrice26' was deleted and will be recreated without data.
- Column 'updateddate27' was deleted and will be recreated without data.
- Column 'MPN27' was deleted and will be recreated without data.
- Column 'Merchant27' was deleted and will be recreated without data.
- Column 'Baseprice27' was deleted and will be recreated without data.
- Column 'Shipping27' was deleted and will be recreated without data.
- Column 'Tax27' was deleted and will be recreated without data.
- Column 'BLPrice27' was deleted and will be recreated without data.

2 solutions

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 · Answer 1 · 2012-05-12T07:52:00

Solution 2

Having 175 columns in a single table may indicate that you should redesign your data structure. Do you really have so many non repeating attributes on a single entity?

Have a look at Database Normalization Basics[^]. Perhaps that would give you ideas to enhance the design.

Posted 12-May-12 7:52am

Wendelius

Comments

Maciej Los 12-May-12 18:05pm

Good answer and link, my 5!

Wendelius 13-May-12 1:47am

Thanks

Maciej Los · Answer 2 · 2012-05-12T06:44:00

Thendral.N,

Take a look at your datatable structure:

Field1	Field ...	Field26	Field27
updateddate1	...	updateddate26	updateddate27
MPN1	...	MPN26	MPN27
Merchant1	...	Merchant26	Merchant27
Baseprice1	...	Baseprice26	Baseprice27
Shipping1	...	Shipping26	Shipping27
Tax1	...	Tax26	Tax27
BLPrice1	...	BLPrice26	BLPrice27

And what you see? Duplicated fields! You need to move some columns into another table.

Take a look at [^] SQL excercises and download[^] Northwind database (install it). You need to understand how the database works and how to design it.

Then re-design your database!

For example:
Main table columns:

ID --> Primary Key (PK) of Main table<br />
...<br />
other columns/fields<br />
...

Details columns

DetID  --> Primary Key Of Details table, autoincrement<br />
MainID  --> Foreign Key (FK) = PK in Main<br />
updateddate<br />
MPN<br />
Merchant<br />
Baseprice<br />
Shipping<br />
Tax<br />
BLPrice

After creation of Details table, use queries like these:

SQL

INSERT INTO Detail (MainID, updateddate, MPN, Merchant, Baseprice, Shipping, Tax, BLPrice)
SELECT ID AS MainID, updateddate1 AS updateddate, MPN1 AS MPN, Merchant1 AS Merchant, Baseprice1 AS Baseprice, Shipping1 AS Shipping, Tax1 AS Tax, BLPrice1 AS BLPrice
FROM Main

...

SQL

INSERT INTO Detail (MainID, updateddate, MPN, Merchant, Baseprice, Shipping, Tax, BLPrice)
SELECT ID AS MainID, updateddate2 AS updateddate, MPN2 AS MPN, Merchant2 AS Merchant, Baseprice2 AS Baseprice, Shipping2 AS Shipping, Tax2 AS Tax, BLPrice2 AS BLPrice
FROM Main

and so on, untill 25(27?)...