CODEWITHSUNDEEP
sql-server
Jason Vondersmith
Jason Vondersmith

Reputation: 147

SQL Insert Into cannot insert NULL

I have set some script that inserts data from an XML file into a SQL database. I am getting the following error.

Cannot insert the value NULL into column 'fighterID', table 'MMA     Database.dbo.FIGHTERStemp'; column does not allow nulls. INSERT fails.

I have fighterID set as the primary key and will not allow NULLS. My intention is to have it number each row as they are inserted. I found one answer that advises to modify the column properties to be the identifier. However it will not let me adjust the columns properties without dropping and adding the table again.

I can do that - but what is the SQL syntax to set the identity specification settings? Am I going about this the right way?

Upvotes: 0

Views: 1481

Answers (2)

Avi
Avi

Reputation: 1145

While inserting data into Primary key you can check the previous max id value and then increment it to next value before you insert a new row.

In SQL, you need to drop table before altering its specification. You can do this by taking backup into temp table then drop your main table and then re insert data from temp table.

Upvotes: 0

Steve Mangiameli
Steve Mangiameli

Reputation: 688

It's pretty simple, just set the field with datatype INT (integer) and follow it with keyword IDENTITY. You can include the seed and increment values; e.g. start at 1, increment by 1, or just use the keyword IDENTITY for a 1,1 default.

CREATE TABLE MMA (FighterID INT IDENTITY (1,1), FighterInfo VARCHAR(MAX))

Upvotes: 2

Related Questions