Insert into select statement that sets all other columns to null

Question

I am trying to run this insert into select statement:

insert into xyz (select t1.column1, t1.column2 from table1 t1, table2 t2 
where t1.column1 = t2.column1)

But get the error: "The number of values assigned is not the same as the number of specified or implied columns or variables"

I want to set all other columns to null (e.g. t1.column3 through t1.column50).

Is there a way to do this without having to explicitly list all the columns out in the select statement?

insert into xyz (select  t1.column1, t1.column2, null as column3, null as column4, null as 
column5... from table1 t1, table2 t2 
where t1.column1 = t2.column1)

score 2 · Accepted Answer · answered Oct 01 '18 at 16:45

You can list the columns in the insert list. E.g.

insert into xyz ( column1, column2 )
select t1.column1, t1.column2
from table1 t1
,    table2 t2 
where t1.column1 = t2.column1

so, for example

create table xyz( column1 int not null, column2 int not null, column3 int);
create table table1( column1 int not null, column2 int not null);
create table table2( column1 int not null);
insert into table1 values (1,2),(2,3);
insert into table2 values (1),(2);
select * from xyz;

gives

 COLUMN1 COLUMN2 COLUMN3
 ------- ------- -------
       1       2    NULL
       2       3    NULL

Insert into select statement that sets all other columns to null

1 Answers1