0

I want to swap columns within Visual Fox Pro 9 in table_1 before inserting its rows into table_2 so as to avoid data losses caused by datatype variations. I tried these two options based on other solutions on stackoverflow, but I get syntax error messages for both command inputs. The name field is of datatype = character(5)and it needs to be after the subdir field.

ALTER table "f:\csp" modify COLUMN name character(5) after subdir

ALTER table "f:\csp" change COLUMN name name character(5) after subdir

I attempted these commands based on solutions here: How to move columns in a MySQL table?

Community
  • 1
  • 1
sda
  • 27
  • 1
  • 5

1 Answers1

1

You never need to change the column order, and you never should rely on column order to do something. For inserting into another table from this one you could simply select the columns in the order you desired (and their column names do not even need to be the same in the case of "insert ... select ... "). ie:

insert into table_2 (subdir, name) ;
select subdir, name from table_1

Another way is to use the xBase commands like:

select table_2
append from table_1

In the case of latter, VFP would do the match on column names.

All in all, relying on column ordering is dangerous. If you really want to do that, then you can still do, in a number of ways. One of them is to select all data into a temp table, recreate the table in the order you want and fill back from temp (might not be as easy as it sounds if there are existing dependencies such as referential integrity - also you need to recreate the indexes).

Cetin Basoz
  • 22,495
  • 3
  • 31
  • 39
  • The *.dbfs* have in excess of 50 columns; therefore *method 2* is very good. – sda Mar 25 '16 at 18:15
  • Method 2 may not work because I need to use `join on` and `where` clauses to insert rows. – sda Mar 25 '16 at 18:58
  • Method 2 still works. You could do a select into a cursor and use "Append from (dbf('yourCrsor')). Method 1 always works, 50 or 254 columns. – Cetin Basoz Mar 25 '16 at 20:31