Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2003
    Posts
    3

    Unanswered: How can I insert a column into a table before another column via T-SQL?

    Please, who can help me to resolve this problem?

    My database is MS SQL 2000....

    Thank you very much!!

  2. #2
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Re: How can I insert a column into a table before another column via T-SQL?

    There is only one way to do this - recreate table. If your table is very big - just use DTS to export and import data.

    BEGIN TRANSACTION
    CREATE TABLE dbo.Tmp_t2
    (
    id int NULL,
    newcolumn int NULL,
    code varchar(10) NULL
    ) ON [PRIMARY]
    GO
    IF EXISTS(SELECT * FROM dbo.t2)
    EXEC('INSERT INTO dbo.Tmp_t2 (id, code)
    SELECT id, code FROM dbo.t2 TABLOCKX')
    GO
    DROP TABLE dbo.t2
    GO
    EXECUTE sp_rename 'dbo.Tmp_t2', 't2', 'OBJECT'
    GO
    COMMIT

  3. #3
    Join Date
    Sep 2003
    Posts
    3

    Re: How can I insert a column into a table before another column via T-SQL?

    Thanks for your reply, first!!

    This way I had think before.

    But, I think that the Enterprise Manager can do this in SQL 2000 visual tool.

    So......Maybe somthing can help us to complete this task.

    I hope so......

  4. #4
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Open your table in design mode through Enterprise Manager, then make the change you want but do not close the window or save your changes.

    Instead, click on the scripting icon (3rd from left on my toolbar), and then copy the script it creates.

    Close your table without saving the changes, and then you can adapt the script you copied to whatever you need.

    blindman

  5. #5
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Smile Re: How can I insert a column into a table before another column via T-SQL?

    Originally posted by Richard Chen
    Thanks for your reply, first!!

    This way I had think before.

    But, I think that the Enterprise Manager can do this in SQL 2000 visual tool.

    So......Maybe somthing can help us to complete this task.

    I hope so......
    Enterprise Manager does the same way. To be honest I took script from EI - in design table click on icon 'Save change script' (third from the left) after some changes done.

  6. #6
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Plagiarist!

  7. #7
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Talking

    Originally posted by blindman
    Plagiarist!
    Idea was in air!

  8. #8
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1

  9. #9
    Join Date
    Jul 2002
    Location
    Village, MD
    Posts
    621

    Cool

    Originally posted by blindman
    I just moved from KY (I had an offer from OH, but in MD is much better).

  10. #10
    Join Date
    Sep 2003
    Posts
    3
    Now I understand what you said.

    It's so kind of you.

    I'II try it right now!!

Posting Permissions

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