I have a situation. I am trying to add a column into a table called CUST but the command ALTER TABLE CUST ADD COLUMN PHONENUM VARCHAR(20) will only add a column to end of the column list. Does anyone know to add a column into a table in a specific location, like between two columns.
Another solution will be to use MMSQL Enterprise Manager but I want to add via a script. I have also noticed column can use the keyword BEFORE to specify a column name but for some reason this does not work in MSSQL2000.
Thanks a lot,
JTLike the location of data in a databse, the ordinal position of a column in a table doesn't matter...
and EM does a drop alert rename...you can see the script...
it'll look like:
BEGIN TRANSACTION
SET QUOTED_IDENTIFIER ON
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
COMMIT
BEGIN TRANSACTION
CREATE TABLE dbo.Tmp_myTable99
(
Col1 sysname NOT NULL,
Colx char(10) NULL,
Col2 sysname NOT NULL
) ON [PRIMARY]
GO
IF EXISTS(SELECT * FROM dbo.myTable99)
EXEC('INSERT INTO dbo.Tmp_myTable99 (Col1, Col2)
SELECT Col1, Col2 FROM dbo.myTable99 TABLOCKX')
GO
DROP TABLE dbo.myTable99
GO
EXECUTE sp_rename N'dbo.Tmp_myTable99', N'myTable99', 'OBJECT'
GO
COMMIT
EDIT: You know, I'm suprised it doesn't do a SELECT INTO instead...
ideas anyone?
Got have something to do with the catalog...|||I have done something similar to what you have and I thought there was something in MSSQL that easy the process. I am dealing with live database and I have over 200000 records on it. This is not an in house issue, therefore, I have write a script that will ask for some type parameters to pass to script and my script will do the rest.
This is my first version
use DOCUWARE
/************************************************** ********************************************/
/* */
/* Purpose: */
/* ~ To be able to insert-column in specific location within a DocuWare table */
/* Parameters are pass to ecript under the [PARAMETER] section. Designed only for MSSQL 2000*/
/* */
/* Parameters: */
/* @.tableNameToModify = Name of the table that you are going to modify */
/* @.newDataType = Enter the new datatype for the new column */
/* @.newDatatypeLenght = Enter the lenght of the datatype, for CHAR and VARCHAR only */
/* @.newName = Enter the name for the new column */
/* @.beforeColumn = Enter the name of a existing column, it will insert that new */
/* column before this column */
/* @.allowNull = 1 to allow blank spaces, if 0, user must enter data on this field */
/* */
/************************************************** ********************************************/
/*. . . .[ V A R I A B L E S ]. . . . */
declare @.tableNameToModify varchar(50),
@.newDataType varchar(50),
@.newDatatypeLenght integer,
@.newName varchar(50),
@.beforeColumn varchar(50),
@.objectId int,
@.allowNull int
/* . . . [ P A R A M E T E R S ] . . . */
select @.tableNameToModify = 'personal'
select @.newDataType = 'VARCHAR'
select @.newDatatypeLenght = 20 --> No more than 40 chars for DocuWare
select @.newName = 'SNN'
select @.beforeColumn = 'DOS'
select @.allowNull = 1 --> 0 = No, 1 = Yes
/*. . . .[ V A R I A B L E S ]. . . . */
declare @.seqNum integer,
@.colId integer,
@.holdEXEStatement varchar(5000),
@.colName varchar(50) ,
@.colDatatype varchar(50),
@.coldLength int,
@.isNullable int,
@.constraintType int,
@.seqIndex int,
@.seqStoper int,
@.stopCounter int,
@.maxNumber int,
@.guider int,
@.finalStoper int,
@.exeStoper int,
@.totalLen int,
@.holdNewColumns varchar(5000),
@.oldTableName varchar(20),
@.newTableName varchar(20),
@.alterTable varchar(200)
/* . . . [ E X E C U T I O N ] . . . */
SET ANSI_WARNINGS OFF
-- Check if the table exist
if exists (select 1 from sysobjects where name = upper(rtrim(@.tableNameToModify)))
begin
/* Select old columns and move to a tmp-table */
select @.objectId = id from sysobjects where name = upper(rtrim(@.tableNameToModify))
create table #holdColumns
(
seqNum integer,
colId integer,
colName varchar(50) ,
colDatatype varchar(50),
coldLength int,
isNullable int,
constraintType int
)
/*Create a cursor to retrive columns*/
declare c_cursor cursor for
select 1,
c.colid,
c.name,
t.name,
c.length,
c.isnullable,
s.status
from syscolumns c(nolock), systypes t(nolock), sysconstraints s(nolock)
where c.id = @.objectId
and c.xtype = t.xtype
and c.id = s.id
and c.name not in ('DW_FULLTEXT', 'DW_INDEXTIME', 'DW_REINDEXTIME', 'DW_ROWSTATE', 'DW_TIMESTAMP') -- not like '%DW_%'
order by c.colid asc
for read only
-- Start inserting the cursor
open c_cursor
-- Get the first one on the list
fetch next from c_cursor
into @.seqNum,
@.colId ,
@.colName ,
@.colDatatype ,
@.coldLength,
@.isNullable ,
@.constraintType
while @.@.fetch_status = 0
begin
-- Insert into a tmp table table
set nocount on
select @.seqIndex = @.seqIndex + 1
insert into #holdColumns
(seqNum, colId, colName, colDatatype, coldLength, isNullable, constraintType)
values (@.seqNum, @.colId, @.colName, @.colDatatype, @.coldLength, @.isNullable, @.constraintType)
-- Get the next one on the list
fetch next from c_cursor
into @.seqNum,
@.colId,
@.colName,
@.colDatatype,
@.coldLength,
@.isNullable,
@.constraintType
end
CLOSE c_cursor
deallocate c_cursor
-- Place in the right order
select @.seqStoper = colId from #holdColumns where colName = rtrim(upper(@.beforeColumn))
create table #rightOrder (
r_colId integer,
r_colName varchar(50),
r_colDatatype varchar(50),
r_lenght integer,
r_isNullable integer )
-- Insert new columns
select @.guider = min(colId) from #holdColumns
while (select colId from #holdColumns where colId = @.guider) <> @.seqStoper
begin
Insert into #rightOrder -- ( r_colId, r_colName, r_colDatatype, r_lenght, r_isNullable )
select colId, colName, colDatatype, coldLength, isNullable from #holdColumns where colId = @.guider
select @.guider = @.guider + 1
end
-- Insert the new column in the order that he user wants
insert into #rightOrder -- ( r_colId, r_colName, r_colDatatype, r_lenght, r_isNullable )
values ( @.guider, @.newName, lower(@.newDataType), @.newDatatypeLenght, @.allowNull)
-- Insert the last columns
select @.finalStoper = max(colId) + 1 from #holdColumns
while (select colId from #holdColumns where colId = @.guider) <> @.finalStoper
begin
Insert into #rightOrder -- ( r_colId, r_colName, r_colDatatype, r_lenght, r_isNullable )
select colId + 1, colName, colDatatype, coldLength, isNullable from #holdColumns where colId = @.guider
select @.guider = @.guider + 1
end
-- Create new table with new datatypes
create table #scriptTable (
scriptSeq integer,
tmpdata varchar(100))
insert #scriptTable
select r_colId, r_colName + case r_colDatatype when 'int' then ' INTEGER '
when 'datetime' then ' DATETIME '
when 'varchar' then ' VARCHAR(' + convert(varchar(5), r_lenght) + ')'
Dharmbir wrote:
Phil Brammer wrote: