Update foreign key reference after insert

Hello,

I am insert data into a table called Persons using the code below.  Basically i am moving data out of the Operations table into the Persons table.

 insert into Persons ( Gender, Lan, Country, Status )
    select 
      [Kon], 
	  (select id from vw_Counties where cast( code as int ) = cast(ope_DistrCode as int )) countyid,
	  1, 1 from [Operations]
    where  pat_id is null and (ope_distrCode is not null or kon is not null)

Open in new window


The Persons table has an identity column called id.

What i would like to do is create a foreign key reference from Operation to Persons.

I have a column in the Operations table called Pat_id and i want to set it to the Id value of the newly created row in Persons.

I wonder if this can be done with an OUTPUT clause, or a Merge statement?
soozhAsked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
You can create a trigger in the Persons table to perform the update whenever an Insert occurs. This solution will keep your business requirements up to date since you don't need to add any extra code for all INSERT INTO table Persons that you have along your application and it also works for massive inserts:
CREATE TRIGGER UpdateOperations ON Persons 
AFTER INSERT  
AS  
    UPDATE Oper
    SET Oper.pat_id = i.id
    FROM Operations AS Oper   
           INNER JOIN inserted AS i ON Oper.Kon = i.Gender
    WHERE  Oper.pat_id IS NULL AND Oper.ope_distrCode IS NOT NULL
GO

Open in new window

NOTE: You may need to include more JOIN filters because I don't think the Gender is enough to get a single row but from your example I couldn't find any better option.
0
 
Pawan KumarDatabase ExpertCommented:
Try like..

ALTER TABLE Operations
ADD CONSTRAINT fk_pat_id FOREIGN KEY REFERENCES Persons(id)

Go

insert into Persons ( Gender, Lan, Country, Status )
select 
    [Kon], 
	(select id from vw_Counties where cast( code as int ) = cast(ope_DistrCode as int )) countyid,
	1, 1 from [Operations]
where  pat_id is null and (ope_distrCode is not null or kon is not null)


UPDATE p 
FROM Operation p SET p.Pat_id = SCOPE_IDENTITY()
[WHERE Clause............ ]


--

Open in new window


Hope it helps !!
0
 
soozhAuthor Commented:
Hello,

Thanks for the replies.

I think that i need to update the Operations table with the value of the idenity column after each insert because as Vitor says the where statement will return more than one row when applying the update.

However if i add a temporary column in the Persons table called OperationsId and pass in the identity of the operations  row i can then use Vitors solution of the insert trigger to apply the foreign key back to the Operations table.

insert into Persons ( Gender, Lan, Country, Status, OperationsId )
    select 
      [Kon], 
	  (select id from vw_Counties where cast( code as int ) = cast(ope_DistrCode as int )) countyid,
	  1, 1,
          [b]id from [Operations][/b]
    where  pat_id is null and (ope_distrCode is not null or kon is not null)

CREATE TRIGGER UpdateOperations ON Persons 
AFTER INSERT  
AS  
    UPDATE Oper
    SET Oper.pat_id = i.id
    FROM Operations AS Oper   
           
    WHERE  Oper.id = i.OperationsId 
GO

Open in new window


However i wonder if something like this is possible:

update Operations set Pat_id = SCOPE_IDENTITY() from

insert into Persons ( Gender, Lan, Country, Status )
    select 
      [Kon], 
	  (select id from vw_Counties where cast( code as int ) = cast(ope_DistrCode as int )) countyid,
	  1, 1 from [Operations]
    where  pat_id is null and (ope_distrCode is not null or kon is not null)

Open in new window

0
A proven path to a career in data science

At Springboard, we know how to get you a job in data science. With Springboard’s Data Science Career Track, you’ll master data science  with a curriculum built by industry experts. You’ll work on real projects, and get 1-on-1 mentorship from a data scientist.

 
Vitor MontalvãoConnect With a Mentor MSSQL Senior EngineerCommented:
However i wonder if something like this is possible:
No because SCOPE_IDENTITY() only works immediately after an INSERT and only returns the last value, meaning that if you for example insert 10 rows only returns the last ID and not the previous 9 rows.
0
 
soozhAuthor Commented:
but that is exactly what i want to do... after each insert i need to update the operations table with the value of identity column of the newly created Person record.
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
I understood your requirement. I was only answering to your question about the "update Operations set Pat_id = SCOPE_IDENTITY() from..." code. That's not possible to do.
0
 
Pawan KumarDatabase ExpertCommented:
Try below..

DECLARE @temp table([Id] INT)

insert into Persons ( Gender, Lan, Country, Status )
OUTPUT SCOPE_IDENTITY() INTO @temp
select 
    [Kon], 
	(select id from vw_Counties where cast( code as int ) = cast(ope_DistrCode as int )) countyid,
	1, 1 from [Operations]
where  pat_id is null and (ope_distrCode is not null or kon is not null)

UPDATE Operations SET Pat_id = (SELECT [Id] FROM @temp)

Open in new window

0
 
Pawan KumarDatabase ExpertCommented:
Hi soozh,
Have you tried my last suggestion? / Any luck with this?

Thank you !

Regards,
Pawan
0
 
Pawan KumarDatabase ExpertCommented:
but that is exactly what i want to do... after each insert i need to update the operations table with the value of identity column of the newly created Person record.

Great that its done. Can you explain me how you are doing this ? - code may be?

Regards,
Pawan
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.