SQL ‘insert into select’ into existing table

I wrote a simple insert into statement:

insert into Worker(WorkingPlace) 
    select WorkingPlace 
    from source.Worker;

I got this error:

Cannot insert the value NULL into column ‘Worker_ID’, table ‘Worker’; column does not allow nulls. INSERT fails

(By the way Worker.WorkingPlace is a nullable column)

I’m very confused, because I’m just inserting data into the ‘WorkingPlace’ column, aren’t I?

Answer

insert into Worker(WorkingPlace) 
    select WorkingPlace 
    from source.Worker;

This will create as many new rows in table Worker as there are rows returned from the select.

So if your query returns 10 rows, then 10 new rows will be inserted into table Worker

This means that any column in that table that is defined as not null must receive a value, either from your query, or get a default value when that is defined.

suppose your table looks like this

create table Worker (id int not null, WorkingPlace varchar(50) null, SomeValue int not null)

then you cannot insert a row like this

insert into Worker (WorkingPlace) values ('something')

This will fail because you have not provided a value for then column id that is defined as not null, and no value for the column SomeValue because that is also defined as not null

if your table looks like

create table Worker (id int primary key not null identity, WorkingPlace varchar(50) null, SomeValue int not null)

then the statement

insert into Worker (WorkingPlace) values ('something')

Will only fail because there is no value for the column SomeValue.
The column id will get its value from the identity

Solution:

insert into Worker (WorkingPlace, SomeValue) values ('something', 123)