CODEWITHSUNDEEP
sqlsql-serversql-server-2008t-sql
user3089119
user3089119

Reputation: 31

Insert into query inserting null into 1st column instead of data into specified column

I am trying to perform a simple insert into, and for some reason it is trying to insert a null value into the 1st column, instead of inserting data into the column I have specified in the query. 

INSERT INTO EquipInfo (OrgID)
SELECT OrgID from Org_Import

Cannot insert the value NULL into column 'MAC', table 'C:\ILoveYouStackOverFlow\Inventory.MDF.dbo.EquipInfo'; column does not allow nulls. INSERT fails.

The EquipInfo table is already in place, MAC is the primary key, and is the only column that doesn't allow nulls. My Org_Import table has one column with no null values in it.

Do I have to provide a value for every single column? I basically just want to append OrgID from Org_Import onto the end of EquipInfo.

All I want to do is add a new column on the end of my EquipInfo table. MAC is short for MAC address, it will controls network access on a vlan.

Upvotes: 1

Views: 2461

Answers (4)

JohnFx
JohnFx

Reputation: 34917

Read the error message more closely

Cannot insert the value NULL into column 'MAC',

It looks like you have another column in table EquipInfo called MAC that doesn't allow nulls.

Two Options:
(1) Add it to your insert statement and assign it a value; or
(2) Change the MAC field to allow null values.
(3) Add a default value on the MAC field

Upvotes: 2

Dour High Arch
Dour High Arch

Reputation: 21722

As everyone else is saying, your database table has at least one other column, MAC, which has a consistency constraint that it cannot be NULL. However, all other answers recommend ways to defeat the constraint (e.g. allow NULLs, set default or garbage values). I believe these answers are irresponsible.

If your database has a constraint, you should assume it is there for a good reason (preventing anomalies, enforcing foreign-keys, et al.). If you do something that violates a constraint, your first impulse should be to satisfy the constraint, not to circumvent it.

In your case, you should find out what MAC is; is it a foreign key? Then determine what the foreign-key value for a record with SELECT OrgID from Org_Import should be and insert that. Is it a primary key? Then make MAC an auto-increment key. Something else? Find out what should be in this column, do not try to circumvent your database integrity.

Upvotes: 1

Mike Christensen
Mike Christensen

Reputation: 91726

The error is pretty clear: EquipInfo has a column called MAC, however your INSERT statement does not provide a value for it. You'll need to provide one; either a constant:

INSERT INTO EquipInfo (MAC, OrgID)
SELECT 0 as MAC, OrgID from Org_Import -- Assuming MAC is numeric

Or, as a value from Org_Import:

INSERT INTO EquipInfo (MAC, OrgID)
SELECT MAC, OrgID from Org_Import -- Assuming MAC is in Org_Import

You could, of course, also drop the NOT NULL constraint on EquipInfo.MAC:

ALTER TABLE EquipInfo ALTER COLUMN MAC whateverTypeMACIs NULL

Or give it a default value:

ALTER TABLE EquipInfo ADD DEFAULT 0 FOR MAC -- Assuming MAC is numeric

Upvotes: 2

D Stanley
D Stanley

Reputation: 152634

It seems like your primary key (MAC) is not set up as an auto-increment field. I would highly recommend adding that. Otherwise you're going to have to figure out a way to generate new primary key values in a way that 1) doesn't collide with existing values and 2) doesn't allow concurrent inserts to add duplicate keys.

Upvotes: 1

Related Questions