Solved

Update foreign key reference after insert

Posted on 2016-11-08
9
34 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
  • 4
  • 3
  • 2
9 Comments
 
LVL 17

Expert Comment

by:Pawan Kumar Khowal
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 45

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
 
LVL 45

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
Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

 

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 45

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 17

Expert Comment

by:Pawan Kumar Khowal
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 17

Expert Comment

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

Thank you !

Regards,
Pawan
0
 
LVL 17

Expert Comment

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

If you have heard of RFC822 date formats, they can be quite a challenge in SQL Server. RFC822 is an Internet standard format for email message headers, including all dates within those headers. The RFC822 protocols are available in detail at:   ht…
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

705 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now