November 26, 2009 at 9:18 am
Hi folks,
I need to add a new column to an existing table. Its default value cannot be null and must be the value of another column in the same table. I read all about the CONSTRAINT ... DEFAULT ... FOR ... but it seems we cannot use the value of another column as a default. I also cannot use computed columns as this new column must be editable.
So basically I must use a trigger, right?
November 26, 2009 at 9:41 am
I would not suggest trigger for this.
Never been in this situation, but I have solution ready.
Post the create table script here, I shall reply with completed script that you can use.
Also what is the table size?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 26, 2009 at 9:50 am
CREATE TABLE [dbo].[ItemLocationHistory](
[IdItemLocationHistory] [uniqueidentifier] NOT NULL,
[IdItemLocation] [int] NOT NULL,
[BarCode] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
[IdItem] [int] NULL,
[CreationDateTime] [datetime] NOT NULL CONSTRAINT [DF__ItemLocat__Creat__093F5D4E] DEFAULT (getdate()),
[IdActor] [int] NULL,
[IdDriver] [int] NULL,
[Model] [varchar](30) COLLATE Latin1_General_CI_AI NULL,
[IsDeletion] [bit] NOT NULL DEFAULT ((0)),
[IdUser] [uniqueidentifier] NULL,
[Comment] [varchar](1000) COLLATE Latin1_General_CI_AI NULL,
CONSTRAINT [PK_ItemLocationHistory] PRIMARY KEY CLUSTERED
(
[IdItemLocationHistory] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
I need to add a [ScanDateTime] to this table which by default will be the same value as [CreationDateTime].
This table has around 500K records.
Thanks for your time.
November 26, 2009 at 10:01 am
Do you need new column to be at the end of the table (last column) or beside the CreationDateTime Column ?
Couldn't ask last time since I did not have the table structure.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 26, 2009 at 10:11 am
At the end would be ok. It doesn't matter.
November 26, 2009 at 10:39 am
Use the script.
-- Create the New table first
CREATE TABLE [ItemLocationHistory_New](
[IdItemLocationHistory] [uniqueidentifier] NOT NULL,
[IdItemLocation] [int] NOT NULL,
[BarCode] [varchar](30) COLLATE SQL_Latin1_General_CP1_CI_AI NOT NULL,
[IdItem] [int] NULL,
[CreationDateTime] [datetime] NOT NULL CONSTRAINT [DF__ItemLocat__Creat__093F5D4E_New] DEFAULT (getdate()),
[ScanDateTime] [datetime] NOT NULL DEFAULT (getdate()),
[IdActor] [int] NULL,
[IdDriver] [int] NULL,
[Model] [varchar](30) COLLATE Latin1_General_CI_AI NULL,
[IsDeletion] [bit] NOT NULL DEFAULT ((0)),
[IdUser] [uniqueidentifier] NULL,
[Comment] [varchar](1000) COLLATE Latin1_General_CI_AI NULL,
CONSTRAINT [PK_ItemLocationHistory_New] PRIMARY KEY CLUSTERED
(
[IdItemLocationHistory] ASC
)WITH (PAD_INDEX = OFF, IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]
-- Move the Data into the newly created table
INSERT INTO [ItemLocationHistory_New]
SELECT [IdItemLocationHistory]
,[IdItemLocation]
,[BarCode]
,[IdItem]
,[CreationDateTime]
,[IdActor]
,[IdDriver]
,[Model]
,[IsDeletion]
,[IdUser]
,[Comment]
,[CreationDateTime]
FROM [ItemLocationHistory]
--Rename the current table to _Hold (If needed to go back), and rename the _New to current tableName
EXEC sp_RENAME 'ItemLocationHistory', 'ItemLocationHistory_Hold'
EXEC sp_RENAME 'ItemLocationHistory_NEW', 'ItemLocationHistory'
As always test it and make sure if it is going to work. Make a copy of the actual table into another test database and test it.
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 26, 2009 at 10:52 am
The
[ScanDateTime] [datetime] NOT NULL DEFAULT (getdate())
will actually get the current date, not the CreationDateTime.
I think this problem needs a trigger. I do not see how it can be resolved in an other way.
November 26, 2009 at 10:57 am
When you insert into the table_new, the values in the CreationDateTime will be inserted into the New Column (ScanDateTime), Default GetDate() would be used when the column value is not specified when inserting.
Have you tried inserting the data using the code given?
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
November 26, 2009 at 11:06 am
ok now it works. I was misunderstanding my own problem.
Thanks for your time. 🙂
November 26, 2009 at 11:19 am
Glad I could hep.. 🙂
Blog -- LearnSQLWithBru
Join on Facebook Page Facebook.comLearnSQLWithBru
Twitter -- BruMedishetty
Viewing 10 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply