Alter Table - Can we add a column in the middle of a table?

Toad Data Modeler
1

Hello,

I am trying to alter a table by adding a column in the middle of a table [ Instead of adding a column at the end].

Please check the below options, I am clear about option 1. I need some clarity on option 2.

Option 1 : Adding a column at the end --> I am able to generate alter script for the new column.
Option 2 : Adding a column in the middle of a table --> Alter scripts drops the table and it recreates the table again with the new column in the middle of a table.

Clarifications-

  1. Does TDM has a feature of generating alter script by adding a column in the middle of a table ?
  2. If I add a column in the middle of a table, alter script should generate as below [ This script is written manually ]
    Syntax - ALTER TABLE “Test_Table” ADD COLUMN “is_reject” TINYINT(1) UNSIGNED DEFAULT 0 NOT NULL AFTER “cleared_by”;

Please advise.

Thanks !!
Rajesh

2

Hello Rajesh,
I suppose you work with MySQL database by your Alter Statement. TDM can not do this alter script at this time, we have registered issue on this TDM-4680, but it has low priority at this time.
TDM by default use temporary tables when generate some Drop and Create statement for change script, so you don’t lost any data. Temporary Table option is set by default in Change Script Wizard.

Example use temporary table:

CREATE TABLE `Entity1_1058177698AF438F9B83E756AD243D40`
(
  `Attribute1` Char(20),
  `Attribute3` Char(20),
  `Attribute2` Char(20)
)
;
INSERT INTO `Entity1_1058177698AF438F9B83E756AD243D40` (`Attribute1`, `Attribute2`) SELECT `Attribute1`, `Attribute2` FROM `Entity1`
;



DROP TABLE `Entity1`
;
ALTER TABLE `Entity1_1058177698AF438F9B83E756AD243D40` RENAME TO `Entity1`
;

Daril