alter table question, order of columns

alter table question, order of columns

Post by Paul Tomsi » Thu, 20 Dec 2001 04:28:36



Is there a way to alter a table so that I can add a column, but not to the
end of the table?

For instance:

CREATE TABLE users(
    user_id int not null primary key identity(1,1),
    username varchar(50),
    date_created datetime not null default current_timestamp

)

GO

Now, I want to add columns, FNAME and LNAME, but I don't want them to be
columns after the date_created as
I'd still like to have the date_created column the LAST column in the table.
Is it possible to now, once the table has been created, and populated with
data, add in FNAME and LNAME
so that my table now appears like

user_id, username, fname, lname, date_created

??

Thanks

Paul

 
 
 

alter table question, order of columns

Post by Dinesh T » Thu, 20 Dec 2001 04:32:56


Paul,

Had a similar discussion yesterday.

You can do the same in SQL enterprise manager(7 and above) using drag and
drop facility or right click and insert column...but basically sqlserver
copies,drops and recreates the table
in the background.

Dinesh.


Quote:> Is there a way to alter a table so that I can add a column, but not to the
> end of the table?

> For instance:

> CREATE TABLE users(
>     user_id int not null primary key identity(1,1),
>     username varchar(50),
>     date_created datetime not null default current_timestamp

> )

> GO

> Now, I want to add columns, FNAME and LNAME, but I don't want them to be
> columns after the date_created as
> I'd still like to have the date_created column the LAST column in the
table.
> Is it possible to now, once the table has been created, and populated with
> data, add in FNAME and LNAME
> so that my table now appears like

> user_id, username, fname, lname, date_created

> ??

> Thanks

> Paul


 
 
 

alter table question, order of columns

Post by Zach Well » Thu, 20 Dec 2001 04:33:45


If you do it within Enterprise manager you can, but that's only because it
drops and recreates the table, which is what you would have to do with TSQL
to accomplish what you want.

--
Zach
---
When in doubt, check out the Books Online,
  it's a GREAT source of information!


Quote:> Is there a way to alter a table so that I can add a column, but not to the
> end of the table?

> For instance:

> CREATE TABLE users(
>     user_id int not null primary key identity(1,1),
>     username varchar(50),
>     date_created datetime not null default current_timestamp

> )

> GO

> Now, I want to add columns, FNAME and LNAME, but I don't want them to be
> columns after the date_created as
> I'd still like to have the date_created column the LAST column in the
table.
> Is it possible to now, once the table has been created, and populated with
> data, add in FNAME and LNAME
> so that my table now appears like

> user_id, username, fname, lname, date_created

> ??

> Thanks

> Paul

 
 
 

alter table question, order of columns

Post by Ben Amad » Thu, 20 Dec 2001 04:50:39


Paul,

In the table's Design View in Enterprise Manager, if you right click on an
existing column where you want to insert a new column, a pop-up menu will
come up and you can select "Insert Column".  That will insert a blank
column, then you can define your new column.

Ben


Quote:> Is there a way to alter a table so that I can add a column, but not to the
> end of the table?

> For instance:

> CREATE TABLE users(
>     user_id int not null primary key identity(1,1),
>     username varchar(50),
>     date_created datetime not null default current_timestamp

> )

> GO

> Now, I want to add columns, FNAME and LNAME, but I don't want them to be
> columns after the date_created as
> I'd still like to have the date_created column the LAST column in the
table.
> Is it possible to now, once the table has been created, and populated with
> data, add in FNAME and LNAME
> so that my table now appears like

> user_id, username, fname, lname, date_created

> ??

> Thanks

> Paul

 
 
 

alter table question, order of columns

Post by Paul Tomsi » Thu, 20 Dec 2001 04:55:23


drops and recreates the table, so what about all the data in there?


> If you do it within Enterprise manager you can, but that's only because it
> drops and recreates the table, which is what you would have to do with
TSQL
> to accomplish what you want.

> --
> Zach
> ---
> When in doubt, check out the Books Online,
>   it's a GREAT source of information!



> > Is there a way to alter a table so that I can add a column, but not to
the
> > end of the table?

> > For instance:

> > CREATE TABLE users(
> >     user_id int not null primary key identity(1,1),
> >     username varchar(50),
> >     date_created datetime not null default current_timestamp

> > )

> > GO

> > Now, I want to add columns, FNAME and LNAME, but I don't want them to be
> > columns after the date_created as
> > I'd still like to have the date_created column the LAST column in the
> table.
> > Is it possible to now, once the table has been created, and populated
with
> > data, add in FNAME and LNAME
> > so that my table now appears like

> > user_id, username, fname, lname, date_created

> > ??

> > Thanks

> > Paul

 
 
 

alter table question, order of columns

Post by Zach Well » Thu, 20 Dec 2001 05:00:13


It copies it off to temp tables.

Something like this:
select * into #Hold from OriginalTable
create table NewTable (fields...)
insert into NewTable
select Fieldlist from #Hold

Of course, it also drops and recreates constraints, etc.

--
Zach
---
When in doubt, check out the Books Online,
  it's a GREAT source of information!


> drops and recreates the table, so what about all the data in there?



> > If you do it within Enterprise manager you can, but that's only because
it
> > drops and recreates the table, which is what you would have to do with
> TSQL
> > to accomplish what you want.

> > --
> > Zach
> > ---
> > When in doubt, check out the Books Online,
> >   it's a GREAT source of information!



> > > Is there a way to alter a table so that I can add a column, but not to
> the
> > > end of the table?

> > > For instance:

> > > CREATE TABLE users(
> > >     user_id int not null primary key identity(1,1),
> > >     username varchar(50),
> > >     date_created datetime not null default current_timestamp

> > > )

> > > GO

> > > Now, I want to add columns, FNAME and LNAME, but I don't want them to
be
> > > columns after the date_created as
> > > I'd still like to have the date_created column the LAST column in the
> > table.
> > > Is it possible to now, once the table has been created, and populated
> with
> > > data, add in FNAME and LNAME
> > > so that my table now appears like

> > > user_id, username, fname, lname, date_created

> > > ??

> > > Thanks

> > > Paul

 
 
 

1. multiple ALTER COLUMNs in one ALTER TABLE statement?

Hello,

I am afraid the answer is no but is there a way to do something like this:

ALTER TABLE mytable
    ALTER COLUMN col1 int,
    col2 float

I am taking a database that has been created by an upsizing wizard from
Visual FoxPro and all the numerics are set to float in SQL Server and I want
to use varchar on longer text fields (I'll ask about that in another post).
since many of the tables have 10 or more fields that need changing I just
used Enterprise Manager to Generate SQL for the database and used my text
editor to change the column values and change from CREATE to ALTER.

What I have now is a script with a lot of ALTER TABLEs that look like the
above but Query Analyzer is not liking it.

Thanks, Tom

2. Baffled by error in my DAO DDL code

3. Help with Alter Table Alter Column

4. sql-mail

5. Alter Table Alter Column Syntax

6. WNetGetUser under NT 3.51 VFP 5.0

7. Alter table alter column

8. Linked Server Datetime Conversion problem

9. alter table alter column

10. Alter table alter column syntax

11. Alter table Alter column problem.

12. ALTER TABLE ... ALTER COLUMN IDENTITY

13. ALTER TABLE ALTER COLUMN