February 8, 2002 at 3:42 am
How can I add a field to a table after a specific other field?
I've tried this but it doesn't work:
alter table [mydatabase].[dbo].[tablename] add [Newfield][bit] DEFAULT 0 NOT NULL AFTER [Oldfield]
February 8, 2002 at 5:08 am
I dont recall seeing "after" supported. Two methods would be to re-create your table in QA putting the column whereever it belongs - this requires bcp'ing the data out to a file and then back into to the new table (or DTS). Or in EM it will let you insert the col wherever you want and does the work for you. Can you explain why it matters though?
Andy
February 8, 2002 at 5:13 am
It only matters to keep it simple.
The table has a lot of fields and they are kind a grouped so it will simply be more logic to put after a certain field.
But I know it is possible in mySQL so I also thought it would be in MSSQL.
February 8, 2002 at 5:18 am
Simple way microsoft does it in EM, but keep in mind this does not transfer permissions. I am working on that, and may cause some other issues that I am missing.
My original table is
CREATE TABLE dbo.Tmp_tblBin
(
WorkingOn binary(4) NOT NULL,
IntA int NULL
) ON [PRIMARY]
Now I want to add a row. Here is what happens at the most basic when EM does it.
CREATE TABLE dbo.Tmp_tblBin
(
WorkingOn binary(4) NOT NULL,
xx char(10) NULL,
IntA int NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.tblBin)
EXEC('INSERT INTO dbo.Tmp_tblBin (WorkingOn, IntA)
SELECT WorkingOn, IntA FROM dbo.tblBin TABLOCKX')
GO
DROP TABLE dbo.tblBin
GO
EXECUTE sp_rename N'dbo.Tmp_tblBin', N'tblBin', 'OBJECT'
GO
I will try to get all the steps and post here in a few days.
February 8, 2002 at 8:08 am
Keep in mind that MySQL has its own extensions to SQL just as SQL Server does. So some of the things that work on one side won't work on another. I believe it was just recently MySQL integrated the Berkeley DB tables to give transaction support (you could do it yourself in previous versions). This represents one difference on the other side of the fence.
The reason EM does it with a temp table is because in order to make changes like this, updates have to happen to the syscolumns table. By default, ad hoc updates to the system tables are disabled. You can change this by using sp_dboption and configuring allow updates' then a RECONFIGURE WITH OVERRIDE.
The following works but is not recommended. I haven't looked at what other system tables look at ColOrder, if any. This is a simple example table, so keep that in mind. Also, when you look at the table as with sp_help, the column names won't be sorted in order by ColOrder. The index on syscolumns is for id, colid. Obviously, column2 will have been created after column5 and thus will have a larger colid value. But INSERT without specifying fields (again not recommended) does work with the proper column order:
USE Sandbox
GO
DROP TABLE TestColumnOrder
GO
CREATE TABLE TestColumnOrder (
Column1 int,
Column3 int,
Column4 int,
Column5 int)
GO
SELECT name, colorder
FROM syscolumns
WHERE OBJECT_ID('TestColumnOrder') = id
GO
ALTER TABLE TestColumnOrder
ADD Column2 int
GO
EXEC sp_configure 'allow updates', 1
RECONFIGURE WITH OVERRIDE
GO
SELECT name, colorder
FROM syscolumns
WHERE OBJECT_ID('TestColumnOrder') = id
GO
UPDATE syscolumns
SET colorder = 2
WHERE name = 'Column2'
AND OBJECT_ID('TestColumnOrder') = id
UPDATE syscolumns
SET colorder = 3
WHERE name = 'Column3'
AND OBJECT_ID('TestColumnOrder') = id
UPDATE syscolumns
SET colorder = 4
WHERE name = 'Column4'
AND OBJECT_ID('TestColumnOrder') = id
UPDATE syscolumns
SET colorder = 5
WHERE name = 'Column5'
AND OBJECT_ID('TestColumnOrder') = id
GO
EXEC sp_configure 'allow updates', 0
RECONFIGURE WITH OVERRIDE
GO
SELECT name, colorder
FROM syscolumns
WHERE OBJECT_ID('TestColumnOrder') = id
GO
INSERT TestColumnOrder VALUES (1, 2, 3, 4, 5)
SELECT * FROM TestColumnOrder
GO
DROP TABLE TestColumnOrder
GO
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
February 8, 2002 at 8:18 am
I think you'd be better served to use a view to tailor the presentation. IMHO. Making these kinds of changes can wreak havoc depending on what is coded against them. Inserts that don't list the col names but rely on col order is one example. Crystal Reports is another. Client apps using a grid that are displaying data in col order and then doing steps based on ordinal position rather than col name.
Andy
February 8, 2002 at 10:44 am
I agree with Andy. If you want to really work with the columns in some order select them in that order. Actually, doing select * everywhere is a bad idea anyway.
Steve Jones
February 8, 2002 at 11:26 am
Basically column order should never matter, it is the way you display that determines what a user will generally see anyway. Column are just for you to control the storage of the data.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply