Help on script optimization - Add fields to a table
-
Hi,
I need help on what's the best way to add fields to a table and be able to order the fields in the manner i want them. For example: CustomerRemarks has the following fields: cCustNo, cRemarks. I want to insert two new fields, cLastUpdatedBy and dLastUpdated between cCustNo and cRemarks. What is the best way to do this?
I currently have this script however I am not sure if this is optimized:
PRINT 'Updating CustomerRemarks table.'
-- Check if table doesn't have the new column
IF (SELECT COLUMNPROPERTY( OBJECT_ID('CustomerRemarks'),'cLastUpdatedBy','AllowsNull')) IS NULL
BEGIN
-- We need to rename the table so that the new table will have the correct ordering of fields
-- Drop the temporary table if it exists
IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustomerRemarks_OLD]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
DROP TABLE CustomerRemarks_OLD
-- Drop the current primay key definition for this table
IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[PK_CustomerRemarks]') and OBJECTPROPERTY(id, N'IsPrimaryKey') = 1)
ALTER TABLE [dbo].[CustomerRemarks] DROP CONSTRAINT PK_CustomerRemarks
-- Rename the table
EXEC sp_rename 'CustomerRemarks', 'CustomerRemarks_Old'
IF NOT exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustomerRemarks]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
PRINT 'Create CustomerRemarks table with new structure'
CREATE TABLE [dbo].[CustomerRemarks] (
[cCustNo] [nvarchar] (50) NOT NULL,
[cLastUpdatedBy] [nvarchar] (50) NOT NULL,
[dLastUpdated] [datetime] NOT NULL,
[cRemarks] [nvarchar] (250) NOT NULL CONSTRAINT DF_CustRemarks DEFAULT 'Not Available',
CONSTRAINT [PK_CustomerRemarks] PRIMARY KEY CLUSTERED
(
[cCustNo]
) ON [PRIMARY]
) ON [PRIMARY]
END
-- Make sure that we have both tables before performing the insert
IF exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustomerRemarks_OLD]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
AND exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[CustomerRemarks]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
BEGIN
INSERT INTO CustomerRemarks (
cCustNo,
cLastUpdatedBy,
dLastUpdated,
cRemarks)
SELECTcCustNo,
'Administrator',
GETDATE(),
cRemarks
FROM CustomerRemarks_OLD
DROP TABLE CustomerRemarks_OLD
END
END
GO
PRINT 'Finished updating table.'
Thanks!
-
I might be missing something here but why not just follow these steps?
- Rename the old table. Clean up indexes and constraints.
- Create a new table ordered as desired with the correct name.
- Move data from old table to new with INSERT.
- Drop old table. (Or save until you are satisfied.)
- Clean up any indexing.
This will get the job done and is basically the same path programs like Erwin would do. Lots less code. Finally, unless you are working with huge tables I don't think that absolute optimization is really necessary for this type of activity. It should normally be a one-off event.
-
Thanks jmcgarvey.
The code I've posted does basically what you've enumerated in the steps.
It also contains some validations just in case something breaks.Just wanted to know what would be the most efficient way to do it.
You are right, this type of activity is only performed once, e.g. upgrading to a newer version of the software or database.
-
why do you care what order the columns are in the table? any selects that target the table should reference the column names directly and not use select *.
if you are using select *, you are asking for trouble
---------------------------------------
elsasoft.org
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply