Inserting Into New Column Produces 'NULL'

Question

PROBLEM

I have added an extra column (nullable VARCHAR) to a table in my test environment.

The column displays correctly and can be referenced elsewhere (stored procedures, applications, etc.) but does not seem to accept data from any new rows. The INSERT statement is successful, but the value for that column is always NULL.

WORKAROUNDS

I am currently using a workaround (insert-then-update), but am curious as to why this is happening in the first place, and would like to get rid of the extraneous operation.

I have tried multiple solutions from similar SO threads (refresh cache, GO, close and reopen, etc.) but haven't had any success.

STEPS TAKEN

Add column to table (Right Click -> Design -> Insert Column -> New nullable VARCHAR column). Table is EntryLog, new column is hostname
GO
Refresh IntelliSense

Try to insert row into table

INSERT INTO EntryLog (employee_id, building_id, hostname) 
VALUES (012345, 54321, 'TestComputer')

Operations returns success, but the new column is not populated.
Select query for new row shows:
<id>/<employee_id>/<building_id>/NULL

The result should be:
<id>/<employee_id>/<building_id>/<hostname>
Refresh IntelliSense again, check table definition, restart SQL service (just in case)
Try Step 4 again with new values and no success

I would start with learning the syntax for adding a new column. The UI is just dreadful. What you are explaining does not make sense. If you provide a value for a column it will not become NULL. Unless perhaps you have an `INSTEAD OF` trigger on your table. — Sean Lange, Aug 25 '23 at 15:11
I can't reproduce your issue: [db<>fiddle](https://dbfiddle.uk/dTrDg_NN) Can you provide a full [mre] here? — Thom A, Aug 25 '23 at 15:14
@SeanLange There was, in fact, an INSTEAD OF INSERT trigger on this table, thank you! The other comments about reproduciblity are correct, and the fact that the statements *weren't* working as expected is what had me scratching my head. I've been doing this for a few years but haven't worked with triggers before, so that didn't even cross my mind. I appreciate the help! — OrcaTime, Aug 25 '23 at 17:12

jarodmwk · Answer 1 · 2023-08-25T15:34:12.637

Trying this out, I wasn't able to reproduce your error but it's possible it's adding the column through the interface is causing your null value error. I recommend altering the table.

CREATE TABLE EntryLog (
    employee_id INT,
    building_id INT,
    hostname VARCHAR(135));

INSERT INTO EntryLog (employee_id,building_id,hostname) 
VALUES (012345,54321,'TestComputer');

select * from EntryLog;

This is the code you gave us. If you want to add a column to an already existing table you should use:

ALTER TABLE EntryLog
ADD NEW_COLUMN VARCHAR(135);

This will add a fourth column to your existing table called NEW_COLUMN with a VARCHAR of 135.

INSERT INTO EntryLog (employee_id,building_id,hostname,NEW_COLUMN)
values (123456,98765,'TESTCOMPUTER2','NEWCELL');

select * from EntryLog;

As you can see, you now have that fourth column. However, if you're wanting to add to an already existing row after adding that row. You should use:

UPDATE EntryLog
SET NEW_COLUMN = 'Updated_Data'
WHERE Employee_ID = 12345;

select * from EntryLog

It turns out the issue was with a trigger on the table, as the code was otherwise sound. Thanks for taking the time to help! — OrcaTime, Aug 25 '23 at 17:15

score 0 · Answer 2 · answered Aug 25 '23 at 17:39

The table has an associated trigger that handles insertions. As this trigger was not updated to include the new column, the column wasn't being written to.

To check if your table has any associated triggers, either use the answer here, or check the Triggers entry under the table in SQL server.

Inserting Into New Column Produces 'NULL'

2 Answers2