Results 1 to 3 of 3
  1. #1
    Join Date
    Mar 2004
    Posts
    2

    Wink Unanswered: After Table Add Column

    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,

    JT

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Like 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:

    Code:
    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...
    Last edited by Brett Kaiser; 03-03-04 at 16:12.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Mar 2004
    Posts
    2
    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) + ')'

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •