update foreign keys on bulk insert
booking has many
I'm working on a db migration to create a booking for all existing appointments without a booking. I have this so far:
INSERT INTO bookings (status) SELECT (status) FROM appointments WHERE appointments.booking_id IS NULL
But I’m stumped with how to update
appointments.booking_id with the newly created bookings.
I'd prefer not to make a new query for each appointment, as there are many existing appointments and performance is a factor. (Normally I rely on the ORM, hence this basic SQL question)
1 Answers update foreign keys on bulk insert
Create a trigger which will insert value to the table after values in booking table are inserted.
CREATE TRIGGER trigger_name ON table_name FOR INSERT AS BEGIN SET nocount ON IF EXISTS (SELECT ( status ) FROM appointments WHERE appointments.booking_id IS NULL) BEGIN INSERT INTO table_name (column_name) SELECT 'xxx' -- Replace this value with what you wanna insert FROM appointments WHERE appointments.booking_id IS NULL END END