user3649739 user3649739 - 7 months ago 9
SQL Question

Insert Records from TableA to TableB using * when TableB missing one field

I have a large table I am trying to add a field to, so far it has been much more efficient due to the table sizes I am working with to do

CREATE TABLE A LIKE B;
INSERT B SELECT * FROM A;


When making changes vs doing any
Alter Table (
Index, FIelds, etc.)

However if I do

CREATE TABLE A LIKE B;
Alter TABLE B ADD Fieldx varchar(100);
INSERT B SELECT * FROM A;


I cannot do that anymore as I get a field count mismatch. Clearly I can "just" do:

CREATE TABLE A LIKE B;
Alter TABLE B ADD Fieldx varchar(100);
INSERT Into B(Field1,..,FieldN) SELECT Field1,...,FieldN FROM A;


However I am trying to do this with a large # of tables each of which has a large # of fields and I've already got a good batch process that works with * and would be much much harder to try to explicitly push each field for each table into each
Insert
and
Select
.

Is there any way to express the

INSERT B SELECT * FROM A;


When B now has 1 more column than A in a way that will not return a count error?

Answer

As long as the new column is added to the end of the original columns, you can do:

INSERT INTO B
SELECT *, ""
FROM A;
Comments