Adding multiple columns with condition
-
Hi - I have an alter command written as below:
ALTER TABLE [dbo].[Cases]
ADD [DocketNumber] NVARCHAR (50) NULL,
[AdministrativeHearingDate] DATETIME DEFAULT ('1900-01-01T00:00:00.000') NULL,
[Locations] NVARCHAR (1000) NULL,
[InterpreterNeeded] BIT DEFAULT ((0)) NOT NULL,
[VoluntaryDisclosure] BIT DEFAULT ((0)) NOT NULL,
[PenaltyAdjustment] FLOAT (53) DEFAULT ((0)) NOT NULL,
[FinancialHardship] INT NULL,
[AdministrativeHearing] INT NULL;I am assuming this statement is adding all those columns so How can I write conditional sql statement that says if exists then add, do I have to write for each individual column or is there any way I can write all this with one sql statement? Any help please? Thanks in advance.
-
Hi - I have an alter command written as below:
ALTER TABLE [dbo].[Cases]
ADD [DocketNumber] NVARCHAR (50) NULL,
[AdministrativeHearingDate] DATETIME DEFAULT ('1900-01-01T00:00:00.000') NULL,
[Locations] NVARCHAR (1000) NULL,
[InterpreterNeeded] BIT DEFAULT ((0)) NOT NULL,
[VoluntaryDisclosure] BIT DEFAULT ((0)) NOT NULL,
[PenaltyAdjustment] FLOAT (53) DEFAULT ((0)) NOT NULL,
[FinancialHardship] INT NULL,
[AdministrativeHearing] INT NULL;I am assuming this statement is adding all those columns so How can I write conditional sql statement that says if exists then add, do I have to write for each individual column or is there any way I can write all this with one sql statement? Any help please? Thanks in advance.
Are you asking how to add a specific column only if a condition exists? That is not possible. You could change the structure to use an Attribute table Cases CaseID - PK DocketNumber Date Locations (should this be an attribute as many are implied) Attributes AttrID CaseID Key - eg InterpreterNeeded Value - Y (I would use a string type for the value field) Then you can select from Cases and inner join the Attributes needed for a particular query.
Never underestimate the power of human stupidity - RAH I'm old. I know stuff - JSOP
-
Hi - I have an alter command written as below:
ALTER TABLE [dbo].[Cases]
ADD [DocketNumber] NVARCHAR (50) NULL,
[AdministrativeHearingDate] DATETIME DEFAULT ('1900-01-01T00:00:00.000') NULL,
[Locations] NVARCHAR (1000) NULL,
[InterpreterNeeded] BIT DEFAULT ((0)) NOT NULL,
[VoluntaryDisclosure] BIT DEFAULT ((0)) NOT NULL,
[PenaltyAdjustment] FLOAT (53) DEFAULT ((0)) NOT NULL,
[FinancialHardship] INT NULL,
[AdministrativeHearing] INT NULL;I am assuming this statement is adding all those columns so How can I write conditional sql statement that says if exists then add, do I have to write for each individual column or is there any way I can write all this with one sql statement? Any help please? Thanks in advance.
Try this:
IF NOT EXISTS (
SELECT *
FROM sys.columns
WHERE object_id = OBJECT_ID(N'dbo.Cases')
AND name = 'DocketNumber'
)
BEGIN
ALTER TABLE [dbo].[Cases]
ADD [DocketNumber] NVARCHAR (50) NULL
ENDBut you have to check and add every column separately.
Wrong is evil and must be defeated. - Jeff Ello
-
Try this:
IF NOT EXISTS (
SELECT *
FROM sys.columns
WHERE object_id = OBJECT_ID(N'dbo.Cases')
AND name = 'DocketNumber'
)
BEGIN
ALTER TABLE [dbo].[Cases]
ADD [DocketNumber] NVARCHAR (50) NULL
ENDBut you have to check and add every column separately.
Wrong is evil and must be defeated. - Jeff Ello
Thanks a lot to add another column - AdministrativeHearingDate, do I need to have another if statement? And thanks for jumping in and helping me buddy.
-
Thanks a lot to add another column - AdministrativeHearingDate, do I need to have another if statement? And thanks for jumping in and helping me buddy.
Yes.
Wrong is evil and must be defeated. - Jeff Ello
-
Yes.
Wrong is evil and must be defeated. - Jeff Ello
Thank you my friend