?
Solved

Update foreign key reference after insert

Posted on 2016-11-08
9
Medium Priority
?
66 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 51

Accepted Solution

by:
Vitor Montalvão earned 2000 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
Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

 
LVL 51

Assisted Solution

by:Vitor Montalvão
Vitor Montalvão earned 2000 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 51

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

Free Backup Tool for VMware and Hyper-V

Restore full virtual machine or individual guest files from 19 common file systems directly from the backup file. Schedule VM backups with PowerShell scripts. Set desired time, lean back and let the script to notify you via email upon completion.  

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
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.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Suggested Courses

752 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