can u Alter a table (add a field)
-
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.
-
If you have DBArtisan its point and click...
OR
1. You can create table with following :
SELECT *
INTO dbo.TABLE1_Backup
FROM dbo.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?
.
-
If you're using SQL Server 7 or SQL Server 2000, just use ALTER TABLE to add a new column.
--Jonathan
--Jonathan -
Jonathan,
Can you provide me with an example? I dont have my SQL book here today
-
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'.
-
Create Table Test (
Col1 Varchar(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!
-
Thanks, After I posted I remembered the BOL, but I think I got too literal with the syntex in the BOL.
Thanks again!
-
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...
.
-
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
--Jonathan -
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.
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply