Insert a field before a specified field in the temporary table

Abstract: Insert a field before a specified field in the temporary table.
Insert a field before a specified field in the temporary table
Problem description:
Use the following Code How can I insert a field before the 'length' field to create a temporary table?
Create Table # temp (Code varchar (50), length varchar (50), color varchar (50), total varchar (50 ))
/*-Generally, the column position is not important. You can write the column list during processing. If you have to handle this, refer to the following example -*/
-- Create a temporary table
Create Table # temp (Code varchar (50), length varchar (50), color varchar (50), total varchar (50 ))
-- Add an alter table # temp add field varchar (50)
Go
-- Adjust the column position and move the newly added field to the front of the length
Exec sp_configure 'Allow updates', 1
Reconfigure with override
Go
Update a set
Colid = case when a. colid = C. colid then B. colid else A. colid + 1 end,
Colorder = case when a. colid = C. colid then B. colid else A. colid + 1 end
From tempdb. DBO. syscolumns,
(Select colid from tempdb. DBO. syscolumns
Where id = object_id ('tempdb. # temp ')
And name = n' length ') B,
(Select colid = max (colid) from tempdb. DBO. syscolumns
Where id = object_id ('tempdb. # temp ') c
Where a. ID = object_id ('tempdb. # temp ')
And a. colid> = B. colid
Go
Exec sp_configure 'Allow updates', 0
Reconfigure with override
Go
-- Display Results
Select * from # temp
-- Delete a temporary table
Drop table # temp
/* -- Result
Total length and color of the new Code Field
---------------------------------------
(The number of affected rows is 0)
--*/

Bytes -----------------------------------------------------------------------------------------

This articleArticleFrom the network, it cannot be verified on sql2005. SQL2000 may be.

The following example shows how to change the sequence of the 'billkid' column in print_bill of SQL2000 table, put it at the beginning, and check whether it is OK.

 

 

The following statements can be used to adjust the column Order (MSS)

Update syscolumns set syscolumns. colid = syscolumns. colid + 1 from syscolumns inner join sysobjects on
Syscolumns. ID = sysobjects. ID and sysobjects. Name = 'print _ Bill'

 

Update syscolumns set syscolumns. colid = 1 from syscolumns inner join sysobjects on
Syscolumns. ID = sysobjects. ID and sysobjects. Name = 'print _ Bill 'and syscolumns. Name = 'billmkid'

 

OracleAvailable onBeforeTo specify the position of the inserted field.

 

BTW: If Special updates to the system directory are not enabled. The system administrator must reconfigureSQL ServerTo allow this operation

OpenMSSQL ServerAttribute---Server options----Enable special updates to the system directory You can.