Alter table add multiple columns ms sql

Question

Can anyone tell me where is the mistake in the following query

ALTER TABLE Countries
ADD ( 
HasPhotoInReadyStorage  bit,
 HasPhotoInWorkStorage  bit,
 HasPhotoInMaterialStorage bit,
 HasText  bit);

ALTER TABLE Regions
ADD ( HasPhotoInReadyStorage  bit,
 HasPhotoInWorkStorage  bit,
 HasPhotoInMaterialStorage bit
 HasText  bit);

ALTER TABLE Provinces
ADD ( HasPhotoInReadyStorage  bit,
 HasPhotoInWorkStorage  bit,
 HasPhotoInMaterialStorage bit
 HasText  bit);


ALTER TABLE Cities
ADD ( HasPhotoInReadyStorage  bit,
 HasPhotoInWorkStorage  bit,
 HasPhotoInMaterialStorage bit
 HasText  bit);

Alter table Hotels
Add 
{
 HasPhotoInReadyStorage  bit,
 HasPhotoInWorkStorage  bit,
 HasPhotoInMaterialStorage bit,
 HasHotelPhotoInReadyStorage  bit,
 HasHotelPhotoInWorkStorage  bit,
 HasHotelPhotoInMaterialStorage bit,
 HasReporterData  bit,
 HasMovieInReadyStorage  bit,
 HasMovieInWorkStorage  bit,
 HasMovieInMaterialStorage bit
};

I get the following errors:

Msg 102, Level 15, State 1, Line 2
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 9
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 15
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 22
Incorrect syntax near '('.
Msg 102, Level 15, State 1, Line 29
Incorrect syntax near '{'.

Answer 1

You need to remove the brackets

ALTER TABLE Countries
ADD  
HasPhotoInReadyStorage  bit,
 HasPhotoInWorkStorage  bit,
 HasPhotoInMaterialStorage bit,
 HasText  bit;

Answer 2

Take out the parentheses and the curly braces, neither are required when adding columns.

Answer 3

this should work in T-SQL

ALTER TABLE Countries  ADD
HasPhotoInReadyStorage  bit,  
HasPhotoInWorkStorage  bit,  
HasPhotoInMaterialStorage bit,  
HasText  bit GO

http://msdn.microsoft.com/en-us/library/ms190273(SQL.90).aspx

Answer 4

Alter table Hotels 
Add  
{ 
 HasPhotoInReadyStorage  bit, 
 HasPhotoInWorkStorage  bit, 
 HasPhotoInMaterialStorage bit, 
 HasHotelPhotoInReadyStorage  bit, 
 HasHotelPhotoInWorkStorage  bit, 
 HasHotelPhotoInMaterialStorage bit, 
 HasReporterData  bit, 
 HasMovieInReadyStorage  bit, 
 HasMovieInWorkStorage  bit, 
 HasMovieInMaterialStorage bit 
}; 

Above you are using {, }.

Also, you are missing commas:

ALTER TABLE Regions 
ADD ( HasPhotoInReadyStorage  bit, 
 HasPhotoInWorkStorage  bit, 
 HasPhotoInMaterialStorage bit <**** comma needed here
 HasText  bit); 

You need to remove the brackets and make sure all columns have a comma where necessary.

Answer 5

Can with default value (T-SQL)

ALTER TABLE
    Regions
ADD
    HasPhotoInReadyStorage BIT NULL, --this column is nullable
    HasPhotoInWorkStorage BIT NOT NULL, --this column is not nullable
    HasPhotoInMaterialStorage BIT NOT NULL DEFAULT(0), --this column is not nullable and set default value is 0(zero)
    HasPhotoInThingStorage BIT NOT NULL CONSTRAINT DF_HasPhotoInThingStorage DEFAULT(0) --this column is not nullable and set default value is 0(zero) with spesific constraint name
GO

Answer 6

ALTER TABLE Regions
ADD ( HasPhotoInReadyStorage  bit,
     HasPhotoInWorkStorage  bit,
     HasPhotoInMaterialStorage bit *(Missing ,)*
     HasText  bit);

Answer 7

Extending the codingbadger's answer to assign a default value

ALTER TABLE Countries
ADD  
HasPhotoInReadyStorage bit DEFAULT 1 WITH VALUES,
 HasPhotoInWorkStorage bit DEFAULT 0 WITH VALUES,
 HasPhotoInMaterialStorage bit DEFAULT 0 WITH VALUES,
 HasText bit DEFAULT 1 WITH VALUES;

Answer 8

ALTER TABLE Countries
ADD  HasPhotoInReadyStorage  bit,
 ADD HasPhotoInWorkStorage  bit,
 ADD HasPhotoInMaterialStorage bit,
 ADD HasText  bit;

ALTER TABLE Regions
ADD  HasPhotoInReadyStorage  bit,
 ADD HasPhotoInWorkStorage  bit,
 ADD HasPhotoInMaterialStorage bit
 ADD HasText  bit;

ALTER TABLE Provinces
ADD  HasPhotoInReadyStorage  bit,
 ADD HasPhotoInWorkStorage  bit,
 ADD HasPhotoInMaterialStorage bit
 ADD HasText  bit;



ALTER TABLE Cities
ADD  HasPhotoInReadyStorage  bit,
 ADD HasPhotoInWorkStorage  bit,
 ADD HasPhotoInMaterialStorage bit
 ADD HasText  bit;

...

in PostgreSQL at 2025

Answer 9

This one worked for me

ALTER TABLE table_name
ADD column1 column1_definition;
ADD column2 column2_definition;
ADD column3 column3_definition;
...
ADD column columnn_definition;