October 9, 2007 at 10:57 pm
Hi,
Can You please help me for this Issue?
I have a table with 3 columns,
I wants to add a new column as 2nd column
rather than end column. but when I am using
SQL> Alter table
add ;
this script added new column in the last column
instead of 2nd position.
I can perform this task by the Enterprise Manager,
But I need the SQL Script to perform this task as i am working in the Client end.
Cheers!
Sandy.
--
October 10, 2007 at 1:49 am
Hi Sandy,
you need to rebuild the table, i.e. create a temporary table with the right column order, copy the date, delete to old table, rename the temporary table.
What you can do is use Management Studio and do the reordering operation, but instead of saving these changes, Management Studio has the ability to script the change. (see the script button, Table Designer -> Generate Change Script)
PS: out of curiosity, why is the order of columns important. If you compare tables I could see a use for the columns to be ordered, but it is a good practice not to use *, and access columns by name explicitly and never by sequence number.
Regards,
Andras
October 10, 2007 at 2:09 am
Order of columns doesn't really have a meaning in SQL. If you name columns in select/insert (which you should) the order that the columns are speified in the metadata is irrelevent.
The way enterprise manager adds a column in the middle of the table is to create a new table, copy the data over, drop the old and then rename. Not fun on big tables.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
October 10, 2007 at 2:30 am
Hey Andras,
Still I have the doubt on this,
My question was:
Can I Add a Column in a specific position of a Table By
using SQL Script, Is it possible or not?
If Yes then How?
If No then Why?
Cheers!
Sandy.
--
October 10, 2007 at 3:22 am
Sandy (10/10/2007)
Hey Andras,Still I have the doubt on this,
My question was:
Can I Add a Column in a specific position of a Table By
using SQL Script, Is it possible or not?
If Yes then How?
If No then Why?
Cheers!
Sandy.
Hi Sandy,
so the quick answer: With an alter table statement you cannot do this. You can do this with the method I mentioned, an example is included at the end of this post.
Why: There is no syntax to allow this. The column_id (internally in SQL Server) also determines the order of the column. But it is used as a key, so shifting columns would require changing all the dependent objects like indexes, ... in the database, so it is a relatively more expensive operation.
Here is the example script (generated by Management Studio)
/* To prevent any potential data loss issues, you should review this script in detail before running it outside the context of the database designer.*/
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET ARITHABORT ON
SET NUMERIC_ROUNDABORT OFF
SET CONCAT_NULL_YIELDS_NULL ON
SET ANSI_NULLS ON
SET ANSI_PADDING ON
SET ANSI_WARNINGS ON
COMMIT
BEGIN TRANSACTION
GO
CREATE TABLE dbo.Tmp_alma
(
a varchar(9) NOT NULL,
insertedcolumn nchar(10) NULL,
b varchar(30) NULL,
c int NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.alma)
EXEC('INSERT INTO dbo.Tmp_alma (a, b, c)
SELECT a, b, c FROM dbo.alma WITH (HOLDLOCK TABLOCKX)')
GO
ALTER TABLE dbo.alma2
DROP CONSTRAINT FK__alma2__b__03317E3D
GO
DROP TABLE dbo.alma
GO
EXECUTE sp_rename N'dbo.Tmp_alma', N'alma', 'OBJECT'
GO
ALTER TABLE dbo.alma ADD CONSTRAINT
PK__alma__7F60ED59 PRIMARY KEY CLUSTERED
(
a
) WITH( STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
GO
COMMIT
BEGIN TRANSACTION
GO
ALTER TABLE dbo.alma2 ADD CONSTRAINT
FK__alma2__b__03317E3D FOREIGN KEY
(
b
) REFERENCES dbo.alma
(
a
) ON UPDATE NO ACTION
ON DELETE NO ACTION
GO
COMMIT
Regards,
Andras
October 10, 2007 at 6:00 am
Hey Andras,
Thanks a Lot,
I got my answer,
Cheers!
Sandy.
--
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply