July 11, 2008 at 9:46 pm
Hi Folks,
I imagine this is easy for most ...but I am struggling
I have a table
CREATE TABLE [dbo].[testtab](
[test1] [char](10) COLLATE Latin1_General_CI_AS NULL,
[test2] [nchar](10) COLLATE Latin1_General_CI_AS NULL
) ON [PRIMARY]
I want to alter it using T-SQL by adding a field between test1 and test2... how is that done?
thanks in advance
July 11, 2008 at 10:14 pm
You have to create a temp table with the new schema. Insert the data from the current table. Delete the current table and then rename the temp table to the old table name.
It is easier to just change it from the GUI. Here is a sample script that the GUI Generates.
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_Table1
(
JobDate datetime NULL,
JobNumber int NULL,
test nchar(10) NULL,
AccountNumber nchar(10) NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.Table1)
EXEC('INSERT INTO dbo.Tmp_Table1 (JobDate, JobNumber, AccountNumber)
SELECT JobDate, JobNumber, AccountNumber FROM dbo.Table1 WITH (HOLDLOCK TABLOCKX)')
GO
DROP TABLE dbo.Table1
GO
EXECUTE sp_rename N'dbo.Tmp_Table1', N'Table1', 'OBJECT'
GO
COMMIT
July 11, 2008 at 10:39 pm
thanks Kent..
I would have thought there would have been an easier way than that. Several sources say there is
a BEFORE keyword with the ALTER TABLE add statement but it don't work for me:rolleyes:
July 11, 2008 at 11:00 pm
If you add the column to the end of the table it is just a simple statement. It really should not matter where the column is in the table anyway.
ALTER TABLE dbo.Table1 ADD
test nchar(10) NULL
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply