SQL server query to add column with default value

Question

I'm trying to change my SQL server database by adding another column to a table with 0 as a default value.

I thought this script worked in the past (for another table), but now I have an error when I try to execute the script

The script

ALTER TABLE Whatever
ADD WhateverColumn tinyint NOT NULL 
DEFAULT(0)

The errors

On hovering mouse over "NOT": Incorrect syntax near 'NOT'. Expecting 'TO'
On hovering mouse over "0": Incorrect syntax near 0. Expecting '(', or SELECT

Anyone knows what's wrong with this?

I just created a new query in my SQL server management studio (v17.7) — RubenHerman
– RubenHerman, Commented Jul 26, 2018 at 11:29
Is this the only command in the batch (or the whole input edit box)? I suspect you have something else in there before the ALTER and as you apparently don't use terminating semicolons that might confuse the parser. — sticky bit
– sticky bit, Commented Jul 26, 2018 at 11:32
Open new query window and paste the code there. Try to execute it. The statement is CORRECT — sepupic
– sepupic, Commented Jul 26, 2018 at 11:38

Fahmi · Accepted Answer · 2018-07-26 11:34:30Z

5

Try this:

ALTER TABLE Whatever
ADD WhateverColumn tinyint NOT NULL DEFAULT 0

answered Jul 26, 2018 at 11:34

Fahmi

37.5k5 gold badges26 silver badges32 bronze badges

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

4 Comments

RubenHerman Over a year ago

This solves the incorrect syntax error on "0" but does not solve the error on the "NOT"

RubenHerman Over a year ago

Msg 102, Level 15, State 1, Line 1 Incorrect syntax near '‌'. Msg 102, Level 15, State 1, Line 2 Incorrect syntax near 'WhateverColumn'.

Fahmi Over a year ago

ALTER TABLE taxi_trip_test_Data add test tinyint NOT NULL DEFAULT 0

Fahmi Over a year ago

I've tried with this and it didn't show me any error

SammuelMiranda · Accepted Answer · 2018-07-26 11:39:03Z

1

Maybe the "Whatever" you are using as the table name has unclosed quotation marks, or even the "WhateverColumn" (both that you place here as a token, i get it) my have this problem, or even the "WhateverColumn" actual name is a reserved word?

answered Jul 26, 2018 at 11:39

SammuelMiranda

4568 silver badges31 bronze badges

2 Comments

RubenHerman Over a year ago

I executed this exact command in the past, but on a different table. I guess the table name shouldn't be the issue?

SammuelMiranda Over a year ago

my point, as complemented by @Singh-amarjeet, is that you can have problems if the column (problably not the table, since it's been created already) has a name like "system" ou "column", or any other name that can be confused with a key word. Try, just to see, with an mame like "tb_whatever", or if you're confident about it, then it should indeed work, and i can't say more over pseudo-code, need database and table declarations to see

Singh Amarjeet · Accepted Answer · 2018-07-26 11:55:00Z

0

@SammuelMiranda has just asked the same just now. It matters if you are using reserved keyword as table or column name also.
you can check this link Add a column with a default value to an existing table in SQL Server

answered Jul 26, 2018 at 11:55

Singh Amarjeet

4510 bronze badges

Comments

RubenHerman · Accepted Answer · 2018-07-26 12:00:46Z

0

As I expected, updating my SQL Server Management Studio to version 17.8.1 solved my issue.
After updating, I was able to run this command without any problem.

answered Jul 26, 2018 at 12:00

RubenHerman

1,8846 gold badges28 silver badges46 bronze badges

Collectives™ on Stack Overflow

SQL server query to add column with default value

4 Answers 4

4 Comments

2 Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

4 Answers 4

4 Comments

2 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related