SQL Server插入报错求助：无法将NULL插入非空列Occupied

Hey there! Let's figure out how to fix this error you're hitting as a SQL Server newbie.

First, let's unpack the error message—it's actually telling you exactly what's wrong: your tblGrave table has an Occupied column that won't accept NULL values, but when you run your INSERT statement, you're not providing any value for this column. SQL tries to fill missing columns with NULL by default, which breaks the rule here, hence the failure.

Also, I noticed a mix-up in your code order: you're trying to insert data into tblGrave before dropping and recreating the table. That's backwards—you need to set up your table first, then add data to it.

Here are a few ways to fix this: #

1. Explicitly add a value for Occupied in your INSERT #

The easiest fix is to include a value for the Occupied column when inserting. Depending on what this column is used for (like a yes/no flag), adjust your query like this:

-- If Occupied is a BIT column (0 = empty, 1 = occupied)
INSERT INTO tblGrave (GraveName, Occupied)
SELECT Grave, 0 FROM tblPlotsandOccupants;

-- If it's a text column (e.g., 'Y' for yes, 'N' for no)
INSERT INTO tblGrave (GraveName, Occupied)
SELECT Grave, 'N' FROM tblPlotsandOccupants;

2. Set a default value for the Occupied column #

If you want SQL to automatically assign a value to Occupied when you don't specify one, add a default constraint. You can do this when creating the table or later:

When creating the table:

-- Drop the table if it exists first
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'tblGrave' AND TABLE_SCHEMA = 'dbo')
DROP TABLE dbo.tblGrave;
GO

-- Create the table with a default for Occupied
CREATE TABLE dbo.tblGrave (
    GraveName VARCHAR(100) NOT NULL, -- Change the data type to match your needs
    Occupied BIT NOT NULL DEFAULT 0, -- Automatically set to 0 (empty) if no value is given
    -- Add any other columns here
);
GO

-- Now your original INSERT will work
INSERT INTO tblGrave (GraveName)
SELECT Grave FROM tblPlotsandOccupants;

Adding a default to an existing table:

ALTER TABLE dbo.tblGrave
ADD CONSTRAINT DF_tblGrave_Occupied DEFAULT 0 FOR Occupied;
GO

-- Now you can run your INSERT without specifying Occupied
INSERT INTO tblGrave (GraveName)
SELECT Grave FROM tblPlotsandOccupants;

3. Fix your code order #

Make sure you always create (or recreate) your table before inserting data. Your original code has the INSERT before the DROP/CREATE step, which doesn't make sense. Follow this flow instead:

• Drop the table if it already exists
• Create the table with the correct structure
• Insert your data into the table

内容的提问来源于stack exchange，提问作者Tat Scagliotti