add a column

[joebeanfish]

How can I add a column to an existing table? I have a lot of data in it that I don't want to lose or have to reimport. I can't find anything about "alter table" in the manual.

[John]

The easiest way is to use a new table. You can either create a new table first, and then use:

INSERT INTO NewTable SELECT ... FROM OldTable;

or you can do it all in one SQL statement:

CREATE TABLE NewTable AS SELECT ... FROM OldTable;

In the SELECT ... FROM OldTable you would specify the new value and column that you want, for example:

SELECT Field1, Field2, '' NewField FROM OldTable;

if you wanted to create a VARCHAR column. To get the old name back you would:

DROP TABLE OldTable;
CREATE TABLE OldTable AS SELECT * FROM NewTable;
DROP TABLE NewTable;

[jgraham]

1) What if I want to add and integer or say byte or blob column to my existing table?

2) Is Thunderstone working on a better way to modify/add columns to an existing table? Most every other RDBMS I've used has a very simple one step procedure for add/modifying columns.

[John]

You could either use the first method above of creating the table manually first, or specify the type with a convert, e.g.

SELECT Field1, Field2, convert(0, 'int') NewIntField FROM OldTable;


We do have plans to add support for ALTER TABLE.

[sabety]

It was my understanding from Kai that the alter SQL command was not supported because it would compromise the performance tuning of Texis database tables. Would adding support for ALTER, have a negative impact on Texis performance?

[John]

For the current scheme ALTER would need to go through the same steps outlined above, and the main benefit would be the reduced amount of SQL that would need to be entered. We are looking at an improved format for Texis tables, which would allow an ALTER to occur rapidly with no impact on performance.

[paulman]

I've tried using the "INSERT INTO NewTable..." method for adding a new column. TEXIS seems to have a problem with the BLOB field I have in the original table. It creates the new table but only half the field for the first record get moved over. The last field that's successfuly copied is the field prior to the BLOB. I tried creating the table without the BLOB and all the data moves over without any problems. Do you have any idea why this is happening?

[John]

Which version of Texis do you have? We will try and replicate it here.

[paulman]

I'm using Solaris SPARC machine.

I don't know if this helps at all but when I try to query the new table I get the first few fields and where the BLOB should be I get

tsql ABEND: signal 11

[John]

Which version does tsql say when it runs, e.g.

Texis Version 03.01.975276256(20001126)