We help IT Professionals succeed at work.

how do you alter the table once it is created with loosing the data? thanks

goodk
goodk asked
on
Medium Priority
218 Views
Last Modified: 2012-05-12
ok, if I create the database table as following, and a day later I want to add a new field " `ae_city` varchar(50) NOT NULL" and modify the ae_name to 80, how would I do it.  I do not want to loose any data




CREATE TABLE `ajax_example` (
  `ae_name` varchar(50) NOT NULL,
  `ae_age` int(11) NOT NULL,
  `ae_sex` varchar(1) NOT NULL,
  `ae_wpm` int(11) NOT NULL,
  PRIMARY KEY  (`ae_name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `ajax_example`
--

INSERT INTO `ajax_example` VALUES ('Jerry Lewis', 120, 'm', 20);
INSERT INTO `ajax_example` VALUES ('Regis Philbin', 75, 'm', 44);
INSERT INTO `ajax_example` VALUES ('Frank Furt', 45, 'm', 87);
INSERT INTO `ajax_example` VALUES ('Jill Hill', 22, 'f', 72);
INSERT INTO `ajax_example` VALUES ('Tracy Sax', 27, 'f', 0);
INSERT INTO `ajax_example` VALUES ('Julie Barker', 35, 'f', 90);
INSERT INTO `ajax_example` VALUES ('Blake Black', 54, 'm', 30);
INSERT INTO `ajax_example` VALUES ('Jack Black', 30, 'm', 20);
INSERT INTO `ajax_example` VALUES ('Martina White', 14, 'f', 114);
INSERT INTO `ajax_example` VALUES ('Martin White', 41, 'm', 32);
INSERT INTO `ajax_example` VALUES ('Jason Biggs', 23, 'm', 4);
INSERT INTO `ajax_example` VALUES ('Colin Smith', 21, 'm', 50);
Comment
Watch Question

Paul MacDonaldDirector, Information Systems
CERTIFIED EXPERT

Commented:
The easiest way is to open SQL Manager, right-click on the table and select "Design".   Then make your changes adn save them.
Paul MacDonaldDirector, Information Systems
CERTIFIED EXPERT

Commented:
No data will be lost as long as you don't mess with any existing columns.
Top Expert 2011

Commented:
You can do this easily by using Sql server management studio, right-click on the table you just created, click on Design.

At the bottom blank line, add the new fieldname, tab to next column and select data type.

That's it.

If you really wish to use code, then

ALTER TABLE MyTable ALTER COLUMN NullCOl NVARCHAR(20) NOT NULL
Top Expert 2011

Commented:
Oops;

ALTER TABLE MyTable ADD COLUMN NullCOl NVARCHAR(20) NOT NULL

Author

Commented:
can you kindly code both line one for adding a fields and second of changing the size of the field - thanks
Top Expert 2011

Commented:
The first one I posted should work for changing size:

ALTER TABLE Mytable ALTER COLUMN fieldname varchar(50)

Just change the size say from 50 to whatever

Author

Commented:
and how do we add a new field in the existing table with the data?
Top Expert 2011
Commented:
You have been given various examples of how to do it.

One way is to use ssms as indicated above.

Another way is to use this code as indicated above:

ALTER TABLE MyTable ADD COLUMN fieldname VARCHAR(20) NOT NULL

Author

Commented:
tnanks

Explore More ContentExplore courses, solutions, and other research materials related to this topic.