SQL query to alter already created table

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

SQL query to alter already created table

Author

Topic

pareekfranksoul
Starting Member

26 Posts

Posted - 2009-01-12 : 03:47:59

Already created Table:

CREATE TABLE [dbo].[ABC](
[i_ClassType] [int] NOT NULL,
[UniqueID] [nvarchar](256) NOT NULL,
[VariantID] [nvarchar](256) NOT NULL,
[DeletedDate] [datetime] NOT NULL
) ON [PRIMARY]

I have to modify it as:

CREATE TABLE [dbo].[ABC](
[i_ClassType] [int] NOT NULL,
[UniqueID] [nvarchar](256) NOT NULL,
[VariantID] [nvarchar](256) NOT NULL DEFAULT (''),
[DeletedDate] [datetime] NOT NULL DEFAULT (getdate()),
CONSTRAINT [PK_ABC] PRIMARY KEY CLUSTERED
(
[i_ClassType] ASC,
[UniqueID] ASC,
[VariantID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

---------------------------------------------------

What i have done - I have import the table and no primary key is created but now how could i alter the table to add clustered primery keys to my already imported/created table.

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-12 : 03:49:10

ALTER TABLE
ADD CONSTRAINT ...

For details, see Books Online.

E 12°55'05.63"
N 56°04'39.26"

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-01-12 : 03:54:59

quote:
F. Adding a DEFAULT constraint to an existing column
The following example creates a table with two columns and inserts a value into the first column, and the other column remains NULL. A DEFAULT constraint is then added to the second column. To verify that the default is applied, another value is inserted into the first column, and the table is queried.

Copy Code
CREATE TABLE dbo.doc_exz ( column_a INT, column_b INT) ;
GO
INSERT INTO dbo.doc_exz (column_a)VALUES ( 7 ) ;
GO
ALTER TABLE dbo.doc_exz
ADD CONSTRAINT col_b_def
DEFAULT 50 FOR column_b ;
GO
INSERT INTO dbo.doc_exz (column_a) VALUES ( 10 ) ;
GO
SELECT * FROM dbo.doc_exz ;
GO
DROP TABLE dbo.doc_exz ;
GO

E 12°55'05.63"
N 56°04'39.26"

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-12 : 03:57:22

alter table abc add constraint df_const default '' for variantid
alter table abc add constraint df_const1 default getdate() for DeletedDate

alter table abc add constraint pk_const primary key (i_classtype,uniqueid,variantid)

pareekfranksoul
Starting Member

26 Posts

Posted - 2009-01-12 : 04:15:31

I am running this query :

ALTER TABLE [dbo].[ABC](
[i_ClassType] [int] NOT NULL,
[UniqueID] [nvarchar](256) NOT NULL,
[VariantID] [nvarchar](256) NOT NULL DEFAULT (''),
[DeletedDate] [datetime] NOT NULL DEFAULT (getdate()),
ADD CONSTRAINT [PK_ABC] PRIMARY KEY CLUSTERED
(
[i_ClassType] ASC,
[UniqueID] ASC,
[VariantID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]

and getting below error:

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near '('.
Msg 319, Level 15, State 1, Line 11
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-12 : 04:18:22

after creating table u have run the alter scripts
CREATE TABLE [dbo].[ABC](
[i_ClassType] [int] NOT NULL,
[UniqueID] [nvarchar](256) NOT NULL,
[VariantID] [nvarchar](256) NOT NULL,
[DeletedDate] [datetime] NOT NULL
) ON [PRIMARY]

creating the table
then run this 3 statements

alter table abc add constraint df_const default '' for variantid
alter table abc add constraint df_const1 default getdate() for DeletedDate
alter table abc add constraint pk_const primary key (i_classtype,uniqueid,variantid)

raky
Aged Yak Warrior

767 Posts

Posted - 2009-01-12 : 04:18:55

Hi, u can't alter the table in a single Alter statement. You have Write alter stmt for each column modification

pareekfranksoul
Starting Member

26 Posts

Posted - 2009-01-12 : 05:01:59

Thank you.

I have altered the columns one by one and my table altered.

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-01-12 : 05:04:34

ur welcome