开发者

Adding Fields To A Table if their no there and setting a default value

Can someone help me create a sql script that will add 2 date fields to a table and set the default value to those fields to 10:30 AM

This isnt compiling for some reason

    SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

ALTER TABLE [dbo].[NickTestTable](
    ADD COLUMN [DTMON_F] [smalldatetime] NULL,
    ADD COLUMN [DTMON_T] [smalldatetime] NULL,
    ADD COLUMN [DTTUES_F] [smalldatetime] NULL,
    ADD COLUMN [DTTUES_T] [smalldatetime] NULL,
    ADD COLUMN [DTWED_F] [smalldatetime] NULL,
    ADD COLUMN [DTWED_T] [smalldatetime] NULL,
    ADD COLUMN [DTTHURS_F] [smalldatetime] NULL,
    ADD COLUMN [DTTHURS_T] [smalldatetime] NULL,
    ADD COLUMN [DTFRI_F] [smalldatetime] NULL,
    ADD COLUMN [DTFRI_T] [smalldatetime] NULL,
    ADD COLUMN [DTSAT_F] [smalldatetime] NULL,
    ADD COLUMN [DTSAT_T] [smalldatetime] NULL,
    ADD COLUMN [DTSUN_F] [smalldatetime] NULL,
    ADD COLUMN [DTSUN_T] [smalldatetime] NULL
) ON [PRIMARY]

GO

ALTER TABLE [dbo].[NickTestTable] 
ADD  CONSTRAINT [DF_NickTestTable_DTMON_F]  DEFAULT ('2011-02-28 10:30:00') FOR [DTMON_F]
GO
ALTER TABLE [dbo].[NickTestTable] 
ADD  CONSTRAINT [DF_NickTestTable_DTMON_T]  DEFAULT ('2011-02-28 22:00:00') FOR [DTMON_T]
GO
ALTER TABLE [dbo].[NickTestTable] 
ADD  CONSTRAINT [DF_NickTestTable_DTTUES_F]  DEFAULT ('2011-02-28 10:30:00') FOR [DTTUES_F]
GO
ALTER TABLE [dbo].[NickTestTable] 
ADD  CONSTRAINT [DF_NickTestTable_DTTUES_T]  DEFAULT ('2011-02-28 22:00:00') FOR [DTTUES_T]
GO
ALTER TABLE [dbo].[NickTestTable] 
ADD  CONSTRAINT [DF_NickTestTable_DTWED_F]  DEFAULT ('2011-02-28 10:30:00') FOR [DTWED_F]
GO
ALTER TABLE [dbo].[NickTestTable] 
ADD  CONSTRAINT [DF_NickTestTable_DTWED_T]  DEFAULT ('2011-02-28 22:00:00') FOR [DTWED_T]
GO
ALTER TABLE [dbo].[NickTestTable] 
ADD  CONSTRAINT [DF_NickTestTable_DTTHURS_F] DEFAULT ('2011-02-28 10:30:00') FOR [DTTHURS_F]
GO
ALTER TABLE [dbo].[NickTestTable] 
ADD  CONSTRAINT [DF_NickTestTable_DTTHURS_T]  DEFAULT ('2011-02-28 22:00:00') FOR [DTTHURS_T]
GO
ALTER TABLE [dbo].[NickTestTable] 
ADD  CONSTRAINT [DF_NickTestTable_DTFRI_F]  DEFAULT ('2011-02-28 10:30:00') FOR [DTFRI_F]
GO
ALTER TABLE [dbo].[NickTestTable] 
ADD  CONSTRAINT [DF_NickTestTable_DTFRI_T]  DEFAULT ('2011-02-28 22:00:00') FOR [DTFRI_T]
GO
ALTER TABLE [dbo].[NickTestTable] 
ADD  CONSTRAINT [DF_NickTestTable_DTSAT_F]  DEFAULT ('2011-02-28 10:30:00') FOR [DTSAT_F]
GO
ALTER TABLE [dbo].[NickTestTable] 
ADD  CONSTRAINT [DF_NickTestTable_DTSAT_T]  DEFAULT ('2011-02-28 22:00:00') FOR [DTSAT_T]
GO
ALTER TABLE [dbo].[NickTestTable] 
ADD  CONSTRAINT [DF_NickTestTable_DTSUN_F]  DEFAULT ('2011-02-28 10:30:00') FOR [DTSUN_F]
GO
ALTER TABLE [dbo].[NickTestTable] 
ADD  CONSTRAINT [DF_NickTestTa开发者_如何学Cble_DTSUN_T]  DEFAULT ('2011-02-28 22:00:00') FOR [DTSUN_T]
GO


I'd suggest you do the following for each of your columns.

ALTER TABLE dbo.[NickTestTable] 
ADD [DTMON_F] smalldatetime
CONSTRAINT DF_NickTestTable_DTMON_F DEFAULT '2011-02-28 10:30:00' NOT NULL

Should tidy the code up a little.

0

上一篇:

下一篇:

精彩评论

暂无评论...
验证码 换一张
取 消

最新问答

问答排行榜