Insert from select

Question

Based on this topic I've encountered a problem with insertions.

My Tests table contains:

TestID    Name
1       test_insert_film
2       test_insert_writer
3       test_insert_location
4       test_delete_film
5       test_delete_writer
6       test_delete_location

I want to insert into my TestTables the id's of the tests with the following sequence:

INSERT INTO TestTables(TestID)
   SELECT TestID
   FROM Tests

But I get:

Msg 515, Level 16, State 2, Line 1
Cannot insert the value NULL into column 'TableID', table 'FilmS.dbo.TestTables'; column does not allow nulls. INSERT fails. The statement has been terminated.

TestTables contains 4 columns, one of them being TestID. Why isn't this working?

Go to your designer, and in your column list you must tick the checkbox to allow nulls for the column TableID. This offcourse, if it can allow nulls which only you know... — Mez, Feb 11 '14 at 10:47
You must provide a value for `TableID` (**not** `TestID`) for your table `TestTables` - that's what the error is saying. You're not currently inserting anything into that column .... — marc_s, Feb 11 '14 at 10:50

Thorsten Dittmar · Accepted Answer · 2014-02-11T11:07:54.620

The column TableID (!) in your table TestTables is not allowed NULL values! This column is not in the list of columns to be filled upon the INSERT, so the default value assumed is NULL. This is why you get the error.

You may need something like:

INSERT INTO TestTables(TestID, TableID)
SELECT TestID, '' FROM Tests

To fill the TableID column with a default value. Maybe also other columns in the TestTables table are affected and need to be treated similarly.

PS: You could also modify the the TestTables definition to provide a default value for the respective columns. If you do so you can leave the above statement as it is.

Insert from select

1 Answers1