SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2000 Forums
 Transact-SQL (2000)
 Alter table - Add new column in between..
 New Topic  Reply to Topic
 Printer Friendly
Next Page
Author Previous Topic Topic Next Topic
Page: of 2

azmi
Starting Member

Malaysia
37 Posts

Posted - 12/09/2005 :  22:01:49  Show Profile  Reply with Quote
/*
hi guys,

I want to add new column (column_new) in between column2 and column3 using sql script.


CREATE TABLE test
(
column1 INT,
column2 INT,
column3 INT,
column4 INT,
)
GO
GRANT ALL ON test TO public
GO
SELECT * FROM test
GO
DROP TABLE test
GO

--ALTER TABLE test ADD colum_new INT
--result
column1 column2 column3 column4 colum_new
----------- ----------- ----------- ----------- -----------

/*what i want is like this */

column1 column2 column_new column3 column4
----------- ----------- ----------- ----------- -----------


any idea..

robvolk
Most Valuable Yak

USA
15681 Posts

Posted - 12/09/2005 :  22:14:20  Show Profile  Visit robvolk's Homepage  Reply with Quote
You have to create a new table with the columns in the order you want, copy the data into it, drop the old table and rename the new table with the old name. ALTER TABLE...ADD can only put columns at the end of the list.
Go to Top of Page

azmi
Starting Member

Malaysia
37 Posts

Posted - 12/09/2005 :  22:27:42  Show Profile  Reply with Quote
Well that what i did previously. That required more time than i thought. Furthermore if i got a lot column in my table and also huge data inside..
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 12/10/2005 :  00:43:07  Show Profile  Reply with Quote
Why not add the column on the end then? (which won't need a "copy over and rename" of the table - although there are potential issues with the efficiency of the layout of the data in the table if you use that approach).

Alternatively, if the data is too big to allow you to mess with it create a separate table with a 1:1 join to the main table.

All compromises over "doing it right", but we're in the real world, right?

Kristen
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 12/10/2005 :  02:25:08  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
You should use Enterprise Manager to know how it scripts the changes. Ordinal Position of the column doesnt matter and you can have it at the place you want in Select statement

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

azmi
Starting Member

Malaysia
37 Posts

Posted - 12/11/2005 :  20:32:40  Show Profile  Reply with Quote
I thought there is other easiest way to do it. Well definitly i'am wrong. Yes there is no easy way in this real world. Thanks guys for your reply :-).
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 12/12/2005 :  00:09:09  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
There technically is an easy way to do this. It's very wrong though. You can change the colid on the syscolumns table. It's very dangerous though and can really mess some things up. The bottom line is this...if the physical order of the columns is important to your application, something has been designed very wrong. Having said that, here is a test script for you to examine that does the exact change you want. Follow the directions very carefully. When you run the script in Query Analyzer, run it in text results mode. Make sure you run it in pieces.

Here is the code:


USE Northwind
GO

PRINT 'This script attempts to reorder a column by updating the system tables themselves. '
CREATE TABLE crazy_test(
	identcol INT IDENTITY(1,1), 
	col1 INT, 
	col2 INT, 
	col3 INT, 
	CONSTRAINT pk_crazy_test PRIMARY KEY NONCLUSTERED (identcol ASC))

CREATE CLUSTERED INDEX idx_crazy_test ON crazy_test(col2 DESC)

INSERT crazy_test(col1, col2, col3)
	SELECT 1,8,9 UNION ALL
	SELECT 2,7,11 UNION ALL
	SELECT 3,6,10 UNION ALL
	SELECT 4,5,12

PRINT 'Order works as expected'
SELECT * FROM crazy_test

PRINT 'Notice the colorder, which you would think "orders" the table columns.'
SELECT so.name AS table_name, sc.name AS column_name, sc.colid, sc.colorder
FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.name = 'crazy_test'

--ONLY RUN THE SCRIPT TO HERE.  TAKE TIME TO LOOK AND SEE WHAT YOU HAVE.
--Look in enterprise manager to see rows are ordered as expected.  Make sure you right-click on the Tables and refresh first.


--Now set the server to allow updates directly on the system tables and update the column order.
EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
EXEC sp_configure 'allow updates',1
GO
RECONFIGURE WITH OVERRIDE
GO
UPDATE sc
SET sc.colorder = 
	CASE 
		WHEN sc.colorder = 3 THEN 2
		WHEN sc.colorder = 2 THEN 3
		ELSE sc.colorder
	END
FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.name = 'crazy_test'
GO

PRINT 'Notice the new colorder.'
SELECT so.name AS table_name, sc.name AS column_name, sc.colid, sc.colorder
FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.name = 'crazy_test'
GO

DBCC FREEPROCCACHE
PRINT 'Order works the same in Query Analyzer though.'
SELECT * FROM crazy_test

PRINT 'The indexes are still also on the right columns.'
EXEC sp_helpindex 'crazy_test'

--ONLY RUN THE SCRIPT TO HERE.  TAKE TIME TO LOOK AND SEE WHAT YOU HAVE.
--If you check the Enterprise Manager table views and the diagrams, the order also has not changed.
----Again, make sure you hit the refresh first in Enterprise Manager before checking.
--The indexes also have not been changed.

--*****Now, we will change the colid like we did the column order.  We'll rerun the test from scratch so it's a fair test.
--DO NOT RUN THIS ON A PRODUCTION SYSTEM.  THIS IS JUST FOR TESTING.

GO
DROP TABLE crazy_test
GO	
EXEC sp_configure 'allow updates',0
GO
RECONFIGURE
GO

CREATE TABLE crazy_test(
	identcol INT IDENTITY(1,1), 
	col1 INT, 
	col2 INT, 
	col3 INT, 
	CONSTRAINT pk_crazy_test PRIMARY KEY NONCLUSTERED (identcol ASC))

CREATE CLUSTERED INDEX idx_crazy_test ON crazy_test(col2 DESC)

INSERT crazy_test(col1, col2, col3)
	SELECT 1,8,9 UNION ALL
	SELECT 2,7,11 UNION ALL
	SELECT 3,6,10 UNION ALL
	SELECT 4,5,12

PRINT 'Order works as expected'
SELECT * FROM crazy_test

PRINT 'Notice the colid.'
SELECT so.name AS table_name, sc.name AS column_name, sc.colid, sc.colorder
FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.name = 'crazy_test'

--ONLY RUN THE SCRIPT TO HERE.  TAKE TIME TO LOOK AND SEE WHAT YOU HAVE.
--Look in enterprise manager to see rows are ordered as expected.  Make sure you right-click on the Tables and refresh first.

--Now set the server to allow updates directly on the system tables and update the column id.
EXEC sp_configure 'show advanced options',1
GO
RECONFIGURE
GO
EXEC sp_configure 'allow updates',1
GO
RECONFIGURE WITH OVERRIDE
GO
UPDATE sc
SET sc.colid = 
	CASE 
		WHEN sc.colid = 3 THEN 2
		WHEN sc.colid = 2 THEN 3
		ELSE sc.colid
	END
FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.name = 'crazy_test'
GO

PRINT 'Notice the new colid.'
SELECT so.name AS table_name, sc.name AS column_name, sc.colid, sc.colorder
FROM sysobjects so INNER JOIN syscolumns sc ON so.id = sc.id
WHERE so.name = 'crazy_test'
GO

DBCC FREEPROCCACHE
PRINT 'Order is now different in Query Analyzer.'
SELECT * FROM crazy_test

PRINT 'We messed up the indexes though.  Notice, it now shows col1 as the indexed column.'
PRINT 'THIS IS WHY THIS SHOULD NEVER BE RAN ON A PRODUCTION SYSTEM.  JUST RECREATE THE TABLE IF YOU'
PRINT 'NEED A NEW ORDER.  ORDER SHOULD NOT BE IMPORTANT IN THE RDMS THOUGH, SO YOU PROBABLY HAVE BAD DESIGN'
EXEC sp_helpindex 'crazy_test'

--ONLY RUN THE SCRIPT TO HERE.  TAKE TIME TO LOOK AND SEE WHAT YOU HAVE.
--If you check the Enterprise Manager table views and the diagrams, the order has been changed both in table design and the diagram tool.

--Erase the test tables and reset the system to not allow system table updates.

GO
DROP TABLE crazy_test
GO	
EXEC sp_configure 'allow updates',0
GO
RECONFIGURE
GO


MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

azmi
Starting Member

Malaysia
37 Posts

Posted - 12/12/2005 :  03:10:39  Show Profile  Reply with Quote
I knew there is others way to do it,thanks derrickleggett!, i had try the code.It's work fine and that what i need. As far as RDMS design concerned,i accept your suggestion and i'll take this alternative approach for my general knowledge and for my code reference:-).
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 12/12/2005 :  04:26:47  Show Profile  Reply with Quote
You would do well to start removing all code that depends on column position Right Now! Most especially if it is based on SELECT * rather than SELECT Col1, Col2, ColA, ColB, ...

Kristen
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 12/12/2005 :  04:39:28  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
quote:
Originally posted by Kristen

You would do well to start removing all code that depends on column position Right Now! Most especially if it is based on SELECT * rather than SELECT Col1, Col2, ColA, ColB, ...

Kristen


Thats exactly I meant also

Ordinal Position of the column doesnt matter and you can have it at the place you want in Select statement


Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

derrickleggett
Pointy Haired Yak DBA

USA
4184 Posts

Posted - 12/12/2005 :  08:39:36  Show Profile  Visit derrickleggett's Homepage  Send derrickleggett an AOL message  Send derrickleggett a Yahoo! Message  Reply with Quote
Make anyone who runs that script above is extremely careful. Running that kind of script on a production server could cause support issues with Microsoft. It IS NOT something they support or recommend. It was meant as a test script only. Just because you can do it doesn't mean you should. You could jump off a tall 50 story building without a parachute. Unless you're an IDIOT though, it's probably not a good idea.

MeanOldDBA
derrickleggett@hotmail.com

When life gives you a lemon, fire the DBA.
Go to Top of Page

azmi
Starting Member

Malaysia
37 Posts

Posted - 12/12/2005 :  22:30:01  Show Profile  Reply with Quote
Thanks for your all idea.That why i like this forum. Well derrickleggett that's very cool.That we can suggest for future 'fear factor' tv programme :-).By the way this is very good discussion.Have a nice day.
Go to Top of Page

eyechart
Flowing Fount of Yak Knowledge

USA
3575 Posts

Posted - 12/12/2005 :  23:27:34  Show Profile  Reply with Quote
Can anyone think of a situation where the column position matters? I thought about this a bit and even tried to write some "bad code" that depended on this and couldn't do it.

Azmi, the column position makes no difference whatsoever. If you have code that shows otherwise I would really like to see it.



-ec
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 12/13/2005 :  01:58:03  Show Profile  Reply with Quote
SELECT *

And then some client logic that does RecordSet(5)

Or the columns are arranged as an array - PriceBreak_1, PriceBreak_2 ... PriceBreak_5 and now we need PriceBreak_6 and our client logic does

FOR ColumnNumber = 1 TO 5 ...

But that sort of programming is nuts!

Kristen
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22769 Posts

Posted - 12/13/2005 :  02:09:14  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
I have seen Newbies using rs(0) than rs("Column_Name") which matters if the ordinal position changes. They are good to go if they use Column_Name than ordinal position

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page

Kristen
Test

United Kingdom
22431 Posts

Posted - 12/13/2005 :  02:53:08  Show Profile  Reply with Quote
"I have seen Newbies using rs(0) "

All the more reason to add the new column on the end, rather than in the middle, eh?

Kristen
Go to Top of Page

Warazen
Starting Member

USA
3 Posts

Posted - 09/13/2007 :  16:46:58  Show Profile  Reply with Quote
The Information_Schema.Columns table has an ORDINAL_POSITION field. Is it possible to simply add the desired columns via ALTER TABLE then change the ORDINAL_POSITION for the table in Information_Schema.Columns?

I haven't tried this, but it seems the following approach may work. This example assumes there is a table with 2 columns. Basically add the new column, assign it to position 1, then assign the old column to position 2.

-- Add the column
IF NOT EXISTS (SELECT * FROM [information_schema].[columns] WHERE table_name = 'TargetTable' AND table_schema = 'dbo' AND column_name = 'TheNewColumn')
BEGIN
ALTER TABLE [dbo].[TargetTable]
ADD TheNewColumn int
END

-- Move the new column's ordinal position to the top.
UPDATE [information_schema].[columns]
SET ORDINAL_POSITION = 1
WHERE table_name = 'TargetTable' AND table_schema = 'dbo' AND column_name = 'TheNewColumn'

-- Move the old column's ordinal position to the bottom.
UPDATE [information_schema].[columns]
SET ORDINAL_POSITION = 2
WHERE table_name = 'TargetTable' AND table_schema = 'dbo' AND column_name = 'TheOldColumn'

Some SQL guru is probably screaming right now that this would screw up something at a system level. That's why I'm posting it here.
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
37287 Posts

Posted - 09/13/2007 :  17:05:38  Show Profile  Visit tkizer's Homepage  Reply with Quote
Never ever attempt to do something like this even if you can once you flip the bit in sp_configure. I doubt it would be allowed anyway since you are attempting to update views.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Warazen
Starting Member

USA
3 Posts

Posted - 09/13/2007 :  17:09:37  Show Profile  Reply with Quote
Should have done more research before posting that.

1. INFORMATION_SCHEMA.Columns is a view to syscolumns.colid.
2. Microsoft doesn't support moving ordinal positions in SQL Server.
3. Enterprise Manager creates a new table, moves data, and renames the table when you reorder columns.

The following thread has the above and additional details.
http://community.netscape.com/n/pfx/forum.aspx?nav=printDiscussion&webtag=ws-msdevapps&tid=24163&redirCnt=1
Go to Top of Page

Tsar
Starting Member

1 Posts

Posted - 09/13/2007 :  17:15:08  Show Profile  Reply with Quote
ORDINAL_POSITION is actually a calculated field, and isn't really stored anywhere. For any column, it is the number of columns in that table with colid's not greater than its own colid.

I use similar logic all the time to calculate rankings, with the rank of a given record being 1 + the number of higher-valued records. It allows for ties and such with a minimum of code (or thinking, which is always a plus for me).

Good luck, and get those ordinal dependencies out of your code ASAP!
Go to Top of Page

Warazen
Starting Member

USA
3 Posts

Posted - 09/13/2007 :  17:19:48  Show Profile  Reply with Quote
Thanks TKizer and Tsar.
Go to Top of Page
Page: of 2 Previous Topic Topic Next Topic  
Next Page
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000