Add New Field Using Scripts

[gust1480]

Hi! Can somebody please tell me how to add a new field on a table between an existing field?! Is it even possible? Or I can only add a new field after the last column?!

 

[ks2000]

If you are into sqlserver

Use Enterprise manager to add new column in between existing columns by inserting the row in between the columns.

Or Try the following script

BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_Dept
(
[Dept id] char(10) NOT NULL,
NewColumn char(10) NULL,
[Dept name] char(10) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Dept)
EXEC('INSERT INTO dbo.Tmp_Dept ([Dept id], [Dept name])
SELECT [Dept id], [Dept name] FROM dbo.Dept TABLOCKX')
GO
ALTER TABLE dbo.Empid
DROP CONSTRAINT FK_Empid_Dept
GO
DROP TABLE dbo.Dept
GO
EXECUTE sp_rename N'dbo.Tmp_Dept', N'Dept', 'OBJECT'
GO
COMMIT







Good Luck
Gopala Krishna Kakani

 

[gust1480]

Thanks Gopala! I'll try that scripts you wrote.

 

[SQLSister]

Be aware that adding a new column in the middle even using Enterprise Manager is a bad practice, especially for a production database. To to this a new table is created, then populated from the old table and the old table is dropped and the new one renamed. This means lots of action in the transaction log and possibly a timeout. Very time consuming and utterly unnecssary as all of your queries should specify the fields you want in the order you want them anyway.

Questions about posting. See faq183-874