insert into xyz select * from abc

[Frank Natoli]

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?

Thanks.

[Chris Hope]

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/

[Bill Karwin]

Frank Natoli wrote:

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?

You could name all the input columns in xyz, and then any you don't
specify will use the default value defined in the DEFAULT clause of each
column when you created table xyz.

INSERT INTO xyz (xcol1, xcol2, xcol3)
SELECT acol1, acol2, acol3 FROM abc;

If you need to specify values other than those DEFAULTs defined for xyz
columns, use Chris Hope's suggestion.

Regards,
Bill K.

[Chris Hope]

Bill Karwin wrote:

Frank Natoli wrote:

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?

You could name all the input columns in xyz, and then any you don't
specify will use the default value defined in the DEFAULT clause of each
column when you created table xyz.

INSERT INTO xyz (xcol1, xcol2, xcol3)
SELECT acol1, acol2, acol3 FROM abc;

If you need to specify values other than those DEFAULTs defined for xyz
columns, use Chris Hope's suggestion.

Heh, another half asleep post by me before... I should have mentioned this
method as well. You only need to do the way I suggested if you need to
specify something other than the default value. The great thing about doing
it Bill's way is if you have an auto-incrementing primary key then you can
leave it out of the columns selected which may cause issues inserting into
the new table.
--
Chris Hope - The Electric Toolbox - http://www.electrictoolbox.com/