Solved

Update foreign key reference after insert

Posted on 2016-11-08
9
48 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 28

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 47

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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
LVL 47

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 47

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 28

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 28

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 28

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

3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

Question has a verified solution.

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

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
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 videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

777 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