Solved

Update foreign key reference after insert

Posted on 2016-11-08
9
60 Views
Last Modified: 2016-11-30
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?
0
Comment
Question by:soozh
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
9 Comments
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41878522
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
 
LVL 50

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 41878582
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
 

Author Comment

by:soozh
ID: 41878736
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
Salesforce Has Never Been Easier

Improve and reinforce salesforce training & adoption using WalkMe's digital adoption platform. Start saving on costly employee training by creating fast intuitive Walk-Thrus for Salesforce. Claim your Free Account Now

 
LVL 50

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 500 total points
ID: 41878758
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
 

Author Comment

by:soozh
ID: 41878819
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
 
LVL 50

Expert Comment

by:Vitor Montalvão
ID: 41878830
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
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41879862
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
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41898549
Hi soozh,
Have you tried my last suggestion? / Any luck with this?

Thank you !

Regards,
Pawan
0
 
LVL 29

Expert Comment

by:Pawan Kumar
ID: 41908230
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

Featured Post

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
This post looks at MongoDB and MySQL, and covers high-level MongoDB strengths, weaknesses, features, and uses from the perspective of an SQL user.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

724 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question