Frank Natoli wrote:
Have two tables "abc" and "xyz", where "xyz" is a superset, column-wise,
of "abc".
Is there any simple way to inject all the rows of "abc" into "xyz"?
Tried "insert into xyz select * from abc" but got a complaint of "column
count doesn't match value count at row 1", which of course is true.
Any way to tell SQL to simply set defaults for the missing columns?
Assuming the columns in abc and xyz are in the same order then you can
simply use select * and add in default values for all the fields that are
not defined in the abc table like so (the table name/alias is required for
the * if you have any vaules before it):
insert into xyz select null, abc.*, null, null, null from abc
Obviously you would need to change my nulls to whatever the appropriate
default values is (which cannot be null if the field is defined "not
null").
If the columns are in a different order then you need to write them all into
the query eg:
insert into xyz select column1, column2, column3, null, null from abc
--
Chris Hope - The Electric Toolbox -
http://www.electrictoolbox.com/