Alter table problem

[redcare]

Hi

Iam trying to add new fields to my database using the Alter table statement to add them to the live server.

The statement I have come up with is this:

ALTER TABLE Products ADD ([ServerMakeID] int NOT NULL,[Asset_Number] nvarchar 250,[Contract] nvarchar 50,[IPAddress] nvarchar 50,[DTEAddress2] nvarchar 50,[RaidCard] nvarchar 50,[License] nvarchar 50)
CONSTRAINT [DF_Products_ServerMakeID] DEFAULT (0)
GO

I would be grateful for any help on if Iam going down the right path or not.
Thanks

 

[JamesLean]

Almost:

ALTER TABLE Products
ADD [ServerMakeID] int NOT NULL CONSTRAINT [DF_Products_ServerMakeID] DEFAULT (0),
  [Asset_Number] nvarchar(250),
  [Contract] nvarchar(50),
  [IPAddress] nvarchar(50),
  [DTEAddress2] nvarchar(50),
  [RaidCard] nvarchar(50),
  [License] nvarchar(50)

--James

 

[osjohnm]

hi

you can't add all the columns at once, you need an ALTER TABLE per column like this:

ALTER TABLE Products
ADD [ServerMakeID] int NOT NULL
GO
ALTER TABLE Products
ADD [Asset_Number] nvarchar(250)
GO
ALTER TABLE Products
ADD [Contract] nvarchar(50)
GO
ALTER TABLE Products
ADD [IPAddress] nvarchar (50)
GO
ALTER TABLE Products
ADD [DTEAddress2] nvarchar (50)
GO
ALTER TABLE Products ADD [RaidCard] nvarchar (50)
GO
ALTER TABLE Products ADD [License] nvarchar (50)
GO
ALTER TABLE Products
WITH NOCHECK ADD CONSTRAINT [DF_Products_ServerMakeID] DEFAULT (0) FOR [SERVERMAKEID]
GO

Hope this helps
John

 

[JamesLean]

John,
I'd have to disagree with you there. Adding multiple columns at once, as per my example, will work fine.

--James

 

[redcare]

I have tested the syntax:

ALTER TABLE Products
ADD [ServerMakeID] int NOT NULL CONSTRAINT [DF_Products_ServerMakeID] DEFAULT (0),
[Asset_Number] nvarchar(250),
[Contract] nvarchar(50),
[IPAddress] nvarchar(50),
[DTEAddress2] nvarchar(50),
[RaidCard] nvarchar(50),
[License] nvarchar(50)

On my test server and it works fine.
Thanks very much for your help.

 

[billum]

John,

Please check SQL Server BOL.Adding multiple columns using ALTER TABLE is possible

Bill

 

[osjohnm]

Hi all

Sorry for the incorrect posting. I once had to add multiple columns and couldn't get it to work but by adding each ADD individually I got to work. My "own" bad syntax has been causing me to go the long way around all this time.

Well, you learn something new everyday.

John