468,505 Members | 1,542 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,505 developers. It's quick & easy.

insert into xyz select * from abc

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.

Jul 20 '05
3 4344
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/
Jul 20 '05
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.
Jul 20 '05
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/
Jul 20 '05

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

16 posts views Thread by Philip Boonzaaier | last post: by
20 posts views Thread by Mark Harrison | last post: by
6 posts views Thread by lenygold via DBMonster.com | last post: by
reply views Thread by NPC403 | last post: by
3 posts views Thread by gieforce | last post: by
reply views Thread by fmendoza | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.