add a column

User avatar
joebeanfish
Posts: 2
Joined: Fri Jun 02, 2000 10:31 am

add a column

Post by 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.
User avatar
John
Site Admin
Posts: 2595
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

add a column

Post by 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;
John Turnbull
Thunderstone Software
jgraham
Posts: 1
Joined: Tue Jan 09, 2001 4:05 pm

add a column

Post by 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.
User avatar
John
Site Admin
Posts: 2595
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

add a column

Post by 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.
John Turnbull
Thunderstone Software
sabety
Posts: 76
Joined: Wed Dec 06, 2000 7:11 am

add a column

Post by 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?
User avatar
John
Site Admin
Posts: 2595
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

add a column

Post by 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.
John Turnbull
Thunderstone Software
paulman
Posts: 40
Joined: Tue Dec 19, 2000 4:08 pm

add a column

Post by 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?
User avatar
John
Site Admin
Posts: 2595
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

add a column

Post by John »

Which version of Texis do you have? We will try and replicate it here.
John Turnbull
Thunderstone Software
paulman
Posts: 40
Joined: Tue Dec 19, 2000 4:08 pm

add a column

Post by 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
User avatar
John
Site Admin
Posts: 2595
Joined: Mon Apr 24, 2000 3:18 pm
Location: Cleveland, OH
Contact:

add a column

Post by John »

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

Texis Version 03.01.975276256(20001126)
John Turnbull
Thunderstone Software
Post Reply