insert new value - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

insert new value

Author

Topic

collie
Constraint Violating Yak Guru

400 Posts

Posted - 2007-07-22 : 11:55:23

Hi,

I have a table called Candidate some of the fields are:
candidate_id primary key, int, identity not null
real_candidate_id int not null
country_id int

I want to insert a new record to the table.

The value for Real_candidate_id must be the same as candidate_id
If the country_id is 2 or 47 then i need to add 9999 before the candidate_id and the value therfore for real_candidate_id will be 9999 and the candidate_id.

I tried the following query but it doesn't work. I get the error:
Cannot insert the value NULL into column 'Real_candidate_id', table 'IIB_America.dbo.Candidate'; column does not allow nulls. INSERT fails.

INSERT INTO Candidate
 (Real_candidate_id, Title, SurName, Given_Names, 
            Address1, city, Post_Code, Country_id) 

values
(

case when @country_id in (2,62,47) then convert(bigint,'9999'+(convert(varchar(20)
, (SCOPE_IDENTITY())
))) else (SCOPE_IDENTITY()) end
,
		@Title, @SurName,
	    @Given_Names, @Address1,@city, @Post_Code, @Country_id
)

Whisky-my beloved dog who died suddenly on the 29/06/06-I miss u so much.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2007-07-22 : 12:04:26

Your design of the table is wrong. You do not know the value of Candidate_ID until AFTER the INSERT happens. SO you'd have to do this in 2 steps. Do the INSERT first. Get the Candidate_ID value, and then do an update for the remaining columns computing the values of the other columns using the ID. For this, you need to set the other columns to allow NULLs.

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources