multi table insert statement
-
Hi all,
I am having trouble inserting data into multiple tables in my application. I have a table Job which has foreign keys from other tables, for example, person. When I add a job to the sytem, I need the person who entered the job to be placed into the person table. Other data needs to be inserted into additional tables.
Is there anyway to do an insert using joins? Or is it best to do several insert statements in a transaction?
I have tried something like the following but it doesn't work:
INSERT INTO Job (job_deadline, job_photo) VALUES ('2006-11-01 00:00:00', NULL)
INSERT INTO Person (person_name, person_phone) VALUES ('TestCustomer', '98765432')
FROM Job INNER JOIN
Person ON Job.Person_id = Person.Person_id INNER JOIN
I'd appreciate any help or advice,
Best regards,
Daniel
-
So Job has a dependency on the Person existing before it can be entered?
Wrap it up in a transaction and have separate INSERT statements.
-
Thats right. A job cannot exist without entering the person record...
So you're basically saying that joining the tables on FK's and doing one insert statement is not possible?
-
I don't believe that it is possible directly.
I have used an INSTEAD OF trigger on a view to achieve something similar but to be honest the simplest way is with 2 INSERT statements in a transaction
-
Thanks for your advice, although now that I've come around to actually write the SQL, I'm a little confused.
My Primary keys are automatically generated by SQL Server...How do I know what these are so as to insert the relevant foreign keys into the tables?
Cheers,
Daniel
-
Use lookup for retrieving necessary ID.
CREATE View ...
AS
SELECT Name_A, Name_B
FROM TableA
Inner Join TableB on TableA.B_ID = TableB.ID
GO
CREATE Trigger .....
ON <View Name>
INSTEAD OF INSERT
AS
Insert into TableB (Name_B)
Select Name_B
FROM inserted i
WHERE NOT EXISTS (select 1 from TableB B where B.NAme_B = i.Name_B)
GROUP BY Name_B
Insert into TableA (Name_A, B_ID)
Select i.Name_A, B.ID
FROM inserted i
INNER JOIN TableB B on i.Name_B = B.Name_B
WHERE NOT EXISTS (select 1 from TableA A where A.Name_A = i.Name_A and A.B_Id = B.Id)
GROUP BY i.Name_A, B.ID
_____________
Code for TallyGenerator -
Hi,
David (above) mentioned that you don't need to use an 'INSTEAD OF' trigger...Is there no way to do just a simple insert if you want to put foregin keys in there?
-
Actually if you look on my trigger you'll realize IT IS "2 INSERT statements in a transaction", exactly as David suggested.
Don't be afraid of triggers. They are really helpful for those who know how to use them.
_____________
Code for TallyGenerator
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply