Insert Into Table B Select * From Table A, but with an extra column in Table B, is it possible?

Question

I have Table A and Table B. Table A has columns A, B and C and Table B has columns A, B, C and D.

If I do the following:

INSERT INTO TableB
SELECT * FROM Table A
WHERE (....)

Will it allow me to do it, even with the extra column that the Table B has? I'm trying to do this in my project, however, they told me that the Table B should have the same columns as Table A but with an extra one.

Thanks.

Answer 1

In the case when columns are not the same in both tables you have to explicitly specify columns in insert and select statements.

So in your particular case it should be

INSERT INTO TableB (A, B, C)
SELECT A, B, C FROM Table A
WHERE (....)

Otherwise, if you're not specifying columns in insert statement, it is supposed in most DBMS that columns should be taken from table definition, and in your case it looks like INSERT INTO TableB (A, B, C, D) - and assumes 4 columns, but SELECT * FROM Table A will generate only 3 columns instead of 4. This is the reason of error you're getting.