1

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.

user3089119
  • 31
  • 3

4 Answers4

2

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
Mike Christensen
  • 88,082
  • 50
  • 208
  • 326
  • I was looking at this, and I thought I can just do an insert into on the end of the table without having to mess with any of the other data. http://stackoverflow.com/questions/13386894/sql-server-insert-example – user3089119 Dec 17 '13 at 21:08
  • An `INSERT` will create new rows in the table. Is this what you're trying to do? If so, these new rows will need to follow the constraints of your table schema such as including any required values. – Mike Christensen Dec 17 '13 at 21:11
  • No, I don't want to create new rows. I just want OrgID on the end of my EquipInfo table. What command should I use? – user3089119 Dec 17 '13 at 21:55
  • You're trying to add a new column to your table? – Mike Christensen Dec 17 '13 at 21:59
  • Yes, I don't want to touch the MAC column because it contains all my mac addresses, which will be controlling network access. If all else fails, i'm just going to rebuild it correctly with the new changes. – user3089119 Dec 17 '13 at 23:27
2

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

JohnFx
  • 34,542
  • 18
  • 104
  • 162
  • 1
    (3) Change the MAC field to have a DEFAULT value – Anon Dec 17 '13 at 21:05
  • MAC is a CHAR(25) (MAC Address) that will be controlling network access. Would a default value of 00-00-00-00-00-00 be a security risk? – user3089119 Dec 17 '13 at 23:33
  • I don't know. I have no idea what your application does. If you are concerned just make it not require a value and leave it null. – JohnFx Dec 18 '13 at 00:15
  • It is an inventory app, to keep track of equipment. I have a switch that will look at a file based on what is in the DB, and determine who gets to surf the web. – user3089119 Dec 18 '13 at 00:53
  • You really have to decide that on your own. I can tell you what the problem with your query is, but I can't do a risk analysis on how your app works in a message forum. – JohnFx Dec 18 '13 at 00:56
  • I understand, I'm not a developer, just an IT guy trying to keep track of inventory, and replace our legacy inventory / network access app. With that in mind, would it be better for an amateur such as myself to edit my build script, and just build the DB and insert the data again? – user3089119 Dec 18 '13 at 01:28
  • 1
    @user3089119, if you didn't create the database schema then it would be better to **find out why the column has a NOT NULL constraint**. It is really irresponsible to go around deleting data and constraints if you don't know what they're for; maybe they are required for database integrity or security. If you investigate and the constraint really isn't needed then consider deleting it, but this should be your last resort, not your first. – Dour High Arch Dec 18 '13 at 02:14
  • A better question than whether inserting a fake MAC is secure or not is why you AREN'T populating the MAC field when someone who designed the system decided that it was a required field. Why not fill in a VALID MAC value? – JohnFx Dec 18 '13 at 03:24
1

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.

D Stanley
  • 149,601
  • 11
  • 178
  • 240
1

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.

Dour High Arch
  • 21,513
  • 29
  • 75
  • 90