Unable to insert values into a column- insert fails with error message

Question

Unable to insert values into a column- insert fails with error message

srikanth.baskaran

SSC Rookie

Points: 26
More actions
September 7, 2014 at 9:15 am

#289764

Dear experts,
Im new to sql server and got stuck in the following .
Ive a stored procedure A which has data has a result of split function from a column X of a main table B.
Ive to insert the values of the stored procedure A into a new table called C and want to join with Table B (COLUMNPK, NEWCOLUMN (varcharmax),null)
when i insert values into table C on NEWCOLUMN i get an error .
code:
insert into Table B (NEWCOLUMN)
exec dbo.spA
error:
Cannot insert NULL into the column 'COLUMNPK', the table B does not allow Null .
The statement has been terminated.
But, im actually inserting the values in NEWCOLUMN and not the other one s per the error message.
steps tried out:
1. tried changing the data type of NEWCOLUMN from NULL to NOT NULL, but the update statement also failed.
2. tools-->options--> table& designer--> unchecked the prevent save changes
also did not help.
Request your help and guidance here.
Regards,
Srikanth Baskaran

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

Eirikur Eiriksson SSC Guru Points: 182920 More actions · Answer 1

srikanth.baskaran (9/7/2014)
Dear experts,
Im new to sql server and got stuck in the following .
Ive a stored procedure A which has data has a result of split function from a column X of a main table B.
Ive to insert the values of the stored procedure A into a new table called C and want to join with Table B (COLUMNPK, NEWCOLUMN (varcharmax),null)
when i insert values into table C on NEWCOLUMN i get an error .
code:
insert into Table B (NEWCOLUMN)
exec dbo.spA
error:
Cannot insert NULL into the column 'COLUMNPK', the table B does not allow Null .
The statement has been terminated.
But, im actually inserting the values in NEWCOLUMN and not the other one s per the error message.
steps tried out:
1. tried changing the data type of NEWCOLUMN from NULL to NOT NULL, but the update statement also failed.
2. tools-->options--> table& designer--> unchecked the prevent save changes
also did not help.
Request your help and guidance here.
Regards,
Srikanth Baskaran

Quick question, is the offending pk column an identity column or where does that value come from?

😎

srikanth.baskaran SSC Rookie Points: 26 More actions · Answer 2

hello,

Thanks for your reply.

Iven't made the pk column a identity column yet, but set it as a primary key.

Im inserting the values of stored procedure in to the other column ir NEWCOLUMN.

Regards

Srikanth Baskaran

Luis Cazares SSC Guru Points: 183706 More actions · Answer 3

The problem is that when you insert the value into NEWCOLUMN, you're inserting NULLS and default values in the other columns to complete the row.

If you have a PK constraint on a column, it can't receive null values. You should either insert the correct values on the PK column or set the identity property to ON on that column.

Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?

How to post data/code on a forum to get the best help: Option 1 / Option 2

whereisSQL? SSCarpal Tunnel Points: 4928 More actions · Answer 4

When I get stuck with some error like this, I like to see what I am inserting into the table.

So I would do an

exec dbo.spA --(without the insert)

and visually see the data being returned