Inserting new field

[gust1480]

Can somebody please tell me how to insert a new field between an inexisting field using sql script?!
Ex.
Table1
Customer Number Customer Address

Want to insert Customer Name between Customer Number & Customer Address.

So may tbale would look like this.

Table1
Customer Number Customer Name Customer Address


Thanks in advance!!

 

[fredericofonseca]

you cant. alter table add column will ad to the end of the table.


If it is really important for you to have the fields in a particular order (why if on a select you can specify which order you need them to be!!!), then you need create a temporary table with the fields you need ordered as you need, copy the old table records into this temp table, delete the old one, and then rename the new one to be the old one.

Some fiddling on constraints needs to be done BEFORE any of these steps.

Best way for you to see what needs to be done is to go to Enterprise Manager, design table and select to insert a new field on the desired position. then have a look at the generated script (one of the button options on that window).



Regards

Frederico Fonseca
SysSoft Integrated Ltd

http://www.syssoft-int.com

 

[SQLSister]

You can move fields around in Enterprise Manager, BUT DO NOT DO THIS unless you have no data or very little data in the table. Becasue Enterprise Manager does exactly what Frederico was talking about , it makes a whole new table and then drops the old table and renames the new one. If you have a lot of records doing something like this will kill your server.

Column order in the table structure is completely unnecessary. Column order should always be set in the select query. (you aren't using select * are you? Is so, you should stop.)


Questions about posting. See faq183-874
Click here to help with Hurricane Relief

 

[gust1480]

thanks guys..i son't want to add a new field at the end cause i hava a table with a float field at the end, when i try to run a program that will insert in that table it doesn't do so because i think its because of the float filed at the end of the table.

anyway, i'll just create a new table. Thanks!