can u Alter a table (add a field)

Question

Post reply

can u Alter a table (add a field)

ghughes

SSCrazy

Points: 2201
More actions
November 18, 2003 at 9:44 am

#82554

I was wondering if there is a way to alter a table to added a field to a table? Looked up the ALTER command, but no luck. Any ideas? Thanks for you response in advance.

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

mybid2003 SSCertifiable Points: 6620 More actions · Answer 1

If you have DBArtisan its point and click...

OR

1. You can create table with following :

SELECT *

INTO dbo.TABLE1_Backup

FROMdbo.TABLE1

2. Drop the existing table (TABLE1). Extract access rights on TABLE1 before dropping

3. Create TABLE1 with new structure.

4. Copy the data back with SELECT based on coloumn names (if you need data).

5. Restore access rights.

We do this all the time. Is there any better way to do it?

.

Jonathan SSC-Insane Points: 20427 More actions · Answer 2

If you're using SQL Server 7 or SQL Server 2000, just use ALTER TABLE to add a new column.

--Jonathan

ghughes SSCrazy Points: 2201 More actions · Answer 3

Jonathan,

Can you provide me with an example? I dont have my SQL book here today

ghughes SSCrazy Points: 2201 More actions · Answer 4

I have tried

ALTER TABLE TEST ALTER COLUMN TESTX VARCHAR(50) NULL ADD ROWGUIDCOL

I am getting an error : Incorrect syntax near the keyword 'ADD'.

Crispin Proctor SSCoach Points: 18161 More actions · Answer 5

Create Table Test (

Col1Varchar(200),

)

Go

Alter Table Test Add Col2 Varchar(200)

Go

See "ALTER TABLE" in BOL for more options. ie NULL, NOT NULL, DEFAULT etc etc

Cheers,

Crispin

Edited by - crappy on 11/18/2003 10:27:46 AM

Cheers,CrispinI can't die, there are too many people who still have to meet me!It's not a bug, SQL just misunderstood me!

ghughes SSCrazy Points: 2201 More actions · Answer 6

Thanks, After I posted I remembered the BOL, but I think I got too literal with the syntex in the BOL.

Thanks again!

mybid2003 SSCertifiable Points: 6620 More actions · Answer 7

I was mistaken. We use this process for two main reasons:

1. If we have to change NULL to NOT NULL

2. For SQL Server 6.5 Boxes.

I overlooked the "ADD" part...

.

Jonathan SSC-Insane Points: 20427 More actions · Answer 8

quote:
I was mistaken. We use this process for two main reasons:
1. If we have to change NULL to NOT NULL
2. For SQL Server 6.5 Boxes.
I overlooked the "ADD" part...



CREATE TABLE #Test(

Id int NULL)

 

INSERT #Test DEFAULT VALUES

 

UPDATE #Test SET Id = 0

WHERE Id IS NULL

 

ALTER TABLE #Test ALTER COLUMN Id int NOT NULL

--Jonathan

wizeguy SSC-Addicted Points: 468 More actions · Answer 9

Hi - Something related to this query..

what's sql internals for adding a column? Does sql server have to recreate the whole table? i.e., does the old table get deleted and a new one made with the added column?

How about's when a column is deleted?

Thx.