Alter table Question in Sql 2000

How to add multiple columns with alter table command in Sql 2000 ?

ALTER TABLE [deneme].[dbo].[Mudurluk]
ADD HarcamaYetkilisi varchar(50) COLLATE Turkish_CI_AS NULL

//below gives error
ADD MaliKontrolYetkilisi varchar(50) COLLATE Turkish_CI_AS NULL ,
ADD Memur varchar(50) COLLATE Turkish_CI_AS NULL ,
ADD Sef varchar(50) COLLATE Turkish_CI_AS NULL ,
ADD MuhasebeYetkilisiYardimcisi varchar(50) COLLATE Turkish_CI_AS NULL

Can you help me with this ?

Thanks alot in advance

Specify ADD only the first time.

ALTER Table query

Hi, I just want to know to turn this:
CREATE TABLE [dbo].[tblTierCs] (
[idTierC] [int] NOT NULL ,
[txtNoEmploye] [varchar] (50) COLLATE French_CI_AS NULL ,
[noSubDomain] [int] NOT NULL ,
[txtNameTierC] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
[noOldTierC] [int] NULL ,
[noRSDTierC] [int] NULL
) ON [PRIMARY]
into this:
CREATE TABLE [dbo].[tblTierCs] (
[idTierC] [int] IDENTITY (1, 1) NOT NULL ,
[txtNoEmploye] [varchar] (50) COLLATE French_CI_AS NULL ,
[noSubDomain] [int] NOT NULL ,
[txtNameTierC] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
[noOldTierC] [int] NULL ,
[noRSDTierC] [int] NULL
) ON [PRIMARY]

using an ALTER TABLE query. I tried using:
ALTER TABLE [dbo].[tblTierCs] ALTER COLUMN [idTierC] [int] IDENTITY
(1, 1) NOT NULL but it's not working. Anyone has any idea how I could
do it? Thanks.Heist (advertiseallyouwant@.hotmail.com) writes:
> Hi, I just want to know to turn this:
> CREATE TABLE [dbo].[tblTierCs] (
> [idTierC] [int] NOT NULL ,
> [txtNoEmploye] [varchar] (50) COLLATE French_CI_AS NULL ,
> [noSubDomain] [int] NOT NULL ,
> [txtNameTierC] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
> [noOldTierC] [int] NULL ,
> [noRSDTierC] [int] NULL
> ) ON [PRIMARY]
> into this:
> CREATE TABLE [dbo].[tblTierCs] (
> [idTierC] [int] IDENTITY (1, 1) NOT NULL ,
> [txtNoEmploye] [varchar] (50) COLLATE French_CI_AS NULL ,
> [noSubDomain] [int] NOT NULL ,
> [txtNameTierC] [varchar] (50) COLLATE French_CI_AS NOT NULL ,
> [noOldTierC] [int] NULL ,
> [noRSDTierC] [int] NULL
> ) ON [PRIMARY]
> using an ALTER TABLE query. I tried using:
> ALTER TABLE [dbo].[tblTierCs] ALTER COLUMN [idTierC] [int] IDENTITY
> (1, 1) NOT NULL but it's not working. Anyone has any idea how I could
> do it? Thanks.

You cannot use ALTER TABLE to change a column into IDENTITY column
(except on SQL Server CE!). One way is to rename the table, create
a new and move over the data. You need to have SET IDENTITY_INSERT
on for the table when you move the data.

You can also do it in Enterprise Mangager - which will renamed and
move data behind the scenes.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||What I ended up doing is using SQL Server Entreprise Manager to
"manually" alter the table and then I used the script generator to
create a script I could then used. Thanks.sql

Alter Table Alter Column

I need to Alter the multiple column of an Existing table
ALter TABLE Address
Alter Coumn Address1 varchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
Address2 varchar(250)COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
Address3 varchar(250)COLLATE SQL_Latin1_General_CP1_CI_AS NULL
But this gives me an error
please advice me on this
thanks
samayYou have to have a separate ALTER TABLE statement for each ALTER COLUMN I'm
afraid.
--
David Portas
SQL Server MVP
--

Alter Table Alter Column

I need to Alter the multiple column of an Existing table
ALter TABLE Address
Alter Coumn Address1 varchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
,
Address2 varchar(250)COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
Address3 varchar(250)COLLATE SQL_Latin1_General_CP1_CI_AS NULL
But this gives me an error
please advice me on this
thanks
samayYou have to have a separate ALTER TABLE statement for each ALTER COLUMN I'm
afraid.
David Portas
SQL Server MVP
--

Alter Table Alter Column

I need to Alter the multiple column of an Existing table
ALter TABLE Address
Alter Coumn Address1 varchar(250) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
Address2 varchar(250)COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
Address3 varchar(250)COLLATE SQL_Latin1_General_CP1_CI_AS NULL
But this gives me an error
please advice me on this
thanks
samay
You have to have a separate ALTER TABLE statement for each ALTER COLUMN I'm
afraid.
David Portas
SQL Server MVP