By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
428,659 Members | 910 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 428,659 IT Pros & Developers. It's quick & easy.

Trouble with Script - Adding column to current table.

P: 2
I am trying to add a column to a current table, with data in it. I am only learning, and i have no idea how to change this to make it work. Here is the script I have right now it, but what it does is delete the whole table and recreates it, adding in the extra column. I don't want that. I want the data that is currently there to stay there and then add anew column. How do I reword this (If possible) to make it work?

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblMailLetterBookPersonTr_FK00]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblMailLetterBookPersonTrack] DROP CONSTRAINT tblMailLetterBookPersonTr_FK00
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[FK_tblMailIncomingPersonTrack_tblMailIncoming]') and OBJECTPROPERTY(id, N'IsForeignKey') = 1)
ALTER TABLE [dbo].[tblMailIncomingPersonTrack] DROP CONSTRAINT FK_tblMailIncomingPersonTrack_tblMailIncoming
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblMailLetterBook_DTrig]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[tblMailLetterBook_DTrig]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblMailLetterBook_UTrig]') and OBJECTPROPERTY(id, N'IsTrigger') = 1)
drop trigger [dbo].[tblMailLetterBook_UTrig]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[LetterbookFacility]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[LetterbookFacility]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[MailFacility]') and OBJECTPROPERTY(id, N'IsView') = 1)
drop view [dbo].[MailFacility]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblMailLetterBook]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblMailLetterBook]
GO

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[tblMailIncoming]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[tblMailIncoming]
GO

CREATE TABLE [dbo].[tblMailLetterBook] (
[LetterNo_ID] [int] IDENTITY (1, 1) NOT NULL ,
[LetterNo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[LetterStyle] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Subject] [varchar] (4000) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[DateMailed] [datetime] NULL ,
[LetterType] [int] NULL ,
[Staff_ID] [int] NULL ,
[DateAdded] [datetime] NULL ,
[FacilityCode] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[tblMailIncoming] (
[Mail_ID] [int] NOT NULL ,
[Old_ID] [int] NULL ,
[DateSent] [datetime] NULL ,
[DateRecvd] [datetime] NULL ,
[Subject] [varchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[RoutedTo] [varchar] (20) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[AssignedFile] [varchar] (30) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,
[Staff_ID] [int] NULL ,
[DateAdded] [datetime] NULL ,
[FacilityCode] [varchar] (5) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
) ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblMailLetterBook] ADD
CONSTRAINT [DF__Temporary__Lette__1FB8AE52] DEFAULT (1) FOR [LetterType],
CONSTRAINT [DF__Temporary__Staff__20ACD28B] DEFAULT (1) FOR [Staff_ID],
CONSTRAINT [DF_tblMailLetterBook_DateAdded] DEFAULT (getdate()) FOR [DateAdded],
CONSTRAINT [aaaaatblMailLetterBook_PK] PRIMARY KEY NONCLUSTERED
(
[LetterNo_ID]
) WITH FILLFACTOR = 98 ON [PRIMARY]
GO

CREATE INDEX [LetterNo] ON [dbo].[tblMailLetterBook]([LetterNo]) WITH FILLFACTOR = 98 ON [PRIMARY]
GO

CREATE INDEX [Staff_ID] ON [dbo].[tblMailLetterBook]([Staff_ID]) WITH FILLFACTOR = 98 ON [PRIMARY]
GO

ALTER TABLE [dbo].[tblMailIncoming] ADD
CONSTRAINT [DF_tblMailIncoming_DateAdded] DEFAULT (getdate()) FOR [DateAdded],
CONSTRAINT [PK_tblMailIncoming] PRIMARY KEY NONCLUSTERED
(
[Mail_ID]
) WITH FILLFACTOR = 98 ON [PRIMARY]
GO

GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[tblMailLetterBook] TO [Admin]
GO

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[tblMailLetterBook] TO [AdminAssistant]
GO

GRANT SELECT , UPDATE , INSERT ON [dbo].[tblMailLetterBook] TO [Staff]
GO

GRANT SELECT ON [dbo].[tblMailLetterBook] TO [Image]
GO

GRANT REFERENCES , SELECT , UPDATE , INSERT , DELETE ON [dbo].[tblMailIncoming] TO [Admin]
GO

GRANT SELECT , UPDATE , INSERT , DELETE ON [dbo].[tblMailIncoming] TO [AdminAssistant]
GO

GRANT SELECT , UPDATE , INSERT ON [dbo].[tblMailIncoming] TO [Staff]
GO

GRANT SELECT ON [dbo].[tblMailIncoming] TO [Image]
GO

ALTER TABLE [dbo].[tblMailLetterBook] ADD
CONSTRAINT [FK_tblMailLetterBook_tblAFacility] FOREIGN KEY
(
[FacilityCode]
) REFERENCES [dbo].[tblAFacility] (
[FacilityCode]
),
CONSTRAINT [FK_tblMailLetterBook_tblLetterCodes] FOREIGN KEY
(
[LetterType]
) REFERENCES [dbo].[tblLetterCodes] (
[LetterCode_ID]
),
CONSTRAINT [FK_tblMailLetterBook_tblPersonnel] FOREIGN KEY
(
[Staff_ID]
) REFERENCES [dbo].[tblPersonnel] (
[Staff_ID]
)
GO

ALTER TABLE [dbo].[tblMailIncoming] ADD
CONSTRAINT [FK_tblMailIncoming_tblAFacility] FOREIGN KEY
(
[FacilityCode]
) REFERENCES [dbo].[tblAFacility] (
[FacilityCode]
),
CONSTRAINT [FK_tblMailIncoming_tblAFacility1] FOREIGN KEY
(
[FacilityCode]
) REFERENCES [dbo].[tblAFacility] (
[FacilityCode]
),
CONSTRAINT [FK_tblMailIncoming_tblPersonnel] FOREIGN KEY
(
[Staff_ID]
) REFERENCES [dbo].[tblPersonnel] (
[Staff_ID]
)
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.LetterbookFacility
AS
SELECT FacilityCode, LetterNo_ID, LetterNo, Subject
FROM dbo.tblMailLetterBook

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO

CREATE VIEW dbo.MailFacility
AS
SELECT Mail_ID, Subject, FacilityCode
FROM dbo.tblMailIncoming

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO


CREATE TRIGGER tblMailLetterBook_DTrig ON dbo.tblMailLetterBook FOR DELETE AS
SET NOCOUNT ON
/* * PREVENT DELETES IF DEPENDENT RECORDS IN 'tblMailLetterBookPersonTrack' */
IF (SELECT COUNT(*) FROM deleted, tblMailLetterBookPersonTrack WHERE (deleted.LetterNo_ID = tblMailLetterBookPersonTrack.LetterNo_ID)) > 0
BEGIN
RAISERROR 44445 'The record can''t be deleted or changed. Since related records exist in table ''tblMailLetterBookPersonTrack'', referential integrity rules would be violated.'
ROLLBACK TRANSACTION
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO

CREATE TRIGGER tblMailLetterBook_UTrig ON dbo.tblMailLetterBook FOR UPDATE AS
SET NOCOUNT ON
/* * CASCADE UPDATES TO 'tblMailLetterBookPersonTrack' */
IF UPDATE(LetterNo_ID)
BEGIN
UPDATE tblMailLetterBookPersonTrack
SET tblMailLetterBookPersonTrack.LetterNo_ID = inserted.LetterNo_ID
FROM tblMailLetterBookPersonTrack, deleted, inserted
WHERE deleted.LetterNo_ID = tblMailLetterBookPersonTrack.LetterNo_ID
END

GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Aug 3 '07 #1
Share this Question
Share on Google+
3 Replies


azimmer
Expert 100+
P: 200
I am trying to add a column to a current table, with data in it. I am only learning, and i have no idea how to change this to make it work. Here is the script I have right now it, but what it does is delete the whole table and recreates it, adding in the extra column. I don't want that. I want the data that is currently there to stay there and then add anew column. How do I reword this (If possible) to make it work?

...
Use "ALTER TABLE", it has an "ADD" option; read the help (at ALTER TABLE).
Aug 6 '07 #2

P: 2
Use "ALTER TABLE", it has an "ADD" option; read the help (at ALTER TABLE).

but would I trash this whole code and just add the the alter table code? (I'm very new at this)
Aug 6 '07 #3

azimmer
Expert 100+
P: 200
but would I trash this whole code and just add the the alter table code? (I'm very new at this)
Sorry I'm too busy now to examine your code in detail but it seems that what it does is drops all entities and recreates them. If that's true and the only point to this is that you want to add a new column (and not, say, make sure all constraints and indexes are correct), you can safely replace the whole stuff with adding the column with ALTER TABLE. However, watch out for entities which reference the new column (if there are any) -- e.g. constraints. If there are any, you have to keep them.
Aug 8 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.