Altering SQL table to add column

Question

I currently have a table with four columns - i wanted to add a fifth column but having some trouble.

I open the table in sql server studio management 2008 and i added the column info like so:

CREATE TABLE [dbo].[Case]
(
    CaseId                  UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
    CaseNumber              NVARCHAR(50) NOT NULL,
    CourtId                 INT NOT NULL,
    DateOpened              DATETIME NOT NULL,
)

my addition:

CREATE TABLE [dbo].[Case]
(
    CaseId                  UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
    CaseNumber              NVARCHAR(50) NOT NULL,
    CaseName                NVARCHAR(50),
    CourtId                 INT NOT NULL,
    DateOpened              DATETIME NOT NULL,
)

After adding CaseName column, i tried executing the table in Management Studio but i got the error message "There is already an object named 'Case' in the database." I tried saving and then building my database hoping that the column will be added but that wasn't successful. I tried a New Query and writing the 'Alter table "case" add CaseName nvarchar(50) but again without luck. It shows that the file is changed with the new column because i saved it but after building my overall database it isn't making any changes. Any helpful tips will be great.

@LittleBobbyTables sorry i just missed to add the DateOpened in the second section of my code. I want to add the CaseName column. Thanks — Masriyah
– Masriyah, Commented Oct 1, 2012 at 17:45

LittleBobbyTables - Au Revoir · Accepted Answer · 2012-10-01 17:44:46Z

25

You want to ALTER, as follows:

ALTER TABLE [dbo].[Case] ADD CaseName NVARCHAR(50)

Better yet, you can check for the existance of the column first:

if not exists (SELECT 1 FROM sysobjects INNER JOIN syscolumns ON 
    sysobjects.id = syscolumns.id 
    WHERE sysobjects.name = N'Case' AND syscolumns.name = N'CaseName')
ALTER TABLE [dbo].[Case] ADD CaseName NVARCHAR(50)

answered Oct 1, 2012 at 17:44

LittleBobbyTables - Au Revoir

33k25 gold badges113 silver badges115 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Vikdor Over a year ago

CASE being a keyword in T-SQL in SQL Server, should be enclosed in quotes.

LittleBobbyTables - Au Revoir Over a year ago

@Vikdor - you're asbolutely right, I completely overlooked that.

Masriyah Over a year ago

Is there a way (syntax) for altering the table in the cmd prompt screen? - I am currently trying this and building my database and hopefully the change will go through

LittleBobbyTables - Au Revoir Over a year ago

@Anima - absolutely, look into the sqlcmd utility. You can run SQL scripts from the command line.

Masriyah Over a year ago

So i tried to create the column from the command line but it keeps telling me either the table itself doesn't exist or that i don't have permission - and i am sure that i have permission. And when i execute the same alter command in server management studio it goes through because when i try to execute it again it tells me that the column cannot be specified more than once.

|

Marc · Accepted Answer · 2012-10-01 17:45:47Z

5

you should try this

 ALTER TABLE [dbo].[Case]

    ADD CaseName NVARCHAR(50)

You are trying to create another table Case but one already exists that's why you have an error. When you want to edit a table, you have to use Alter table

answered Oct 1, 2012 at 17:45

Marc

16.6k20 gold badges80 silver badges121 bronze badges

Comments

Beth · Accepted Answer · 2012-10-01 17:51:17Z

1

Use an Alter table statement instead of Create

If you can't get the Alter statement to work for some reason, you could also drop the existing table and create a new one with the new field, but all your existing rows will be lost.

If you're using SSMS, you can Design the table instead of Edit to add the column.

edited Oct 1, 2012 at 17:51

answered Oct 1, 2012 at 17:42

Beth

9,6171 gold badge26 silver badges44 bronze badges

4 Comments

Masriyah Over a year ago

I tried doing that but my problem is when i try to execute it will not update the table with the changes and when i build my database the alterations to the table are being ignored.

Damien_The_Unbeliever Over a year ago

@Amina -Define what you mean by "when I build my database" - are you using some other tool here that you've not mentioned?

Masriyah Over a year ago

@Damien_The_Unbeliever ok so i have all these tables and stored procedures and i make any changes to a stored procedure or a table i have to build the whole database and load with the info. Here is where i believe my problem is - that when i build my new changes to the table are not overwriting the info of the old table (Case table with the new CaseName column added to it.)

Beth Over a year ago

when you say 'build the whole database,' are you dropping and recreating the objects with SQL script? If so, you could be overwriting any changes you've made.

Sirar Salih · Accepted Answer · 2016-01-13 12:01:16Z

1

I recommend checking for the existence of the column prior to adding it, especially important when you work with migration scripts.

Here is how I usually do it:

IF NOT EXISTS(SELECT * FROM sys.columns  WHERE Name = N'ColumnName' AND Object_ID = Object_ID(N'TableName'))
BEGIN
    ALTER TABLE [dbo].TableName ADD ColumnName NVARCHAR(512) null
END

answered Jan 13, 2016 at 12:01

Sirar Salih

2,5632 gold badges20 silver badges18 bronze badges

Comments

whytheq · Accepted Answer · 2012-10-01 19:50:24Z

0

ALTER is what you need to investigate (F1)

An alternative is.
Create a new table

CREATE TABLE [dbo].[Case2]
(
    CaseId                  UNIQUEIDENTIFIER DEFAULT (newid()) NOT NULL,
    CaseNumber              NVARCHAR(50) NOT NULL,
    CourtId                 INT NOT NULL,
    DateOpened              DATETIME NOT NULL,
newcolumn               INT NULL    
)

Move data from existing table into the new one

INSERT INTO [dbo].[Case2]
SELECT * FROM [dbo].[Case]

Then

DROP TABLE [dbo].[Case]

Then in management studio right-click 'Case2' and re-name it 'Case'

answered Oct 1, 2012 at 19:50

whytheq

35.7k69 gold badges179 silver badges283 bronze badges

Collectives™ on Stack Overflow

Altering SQL table to add column

5 Answers 5

6 Comments

Comments

4 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

6 Comments

Comments

4 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related