Inserting multiple new records into a DB 1

[nickmollberg]

I have three tables, 'appointment', 'appointmentdetailstemplate' and 'appointmentdetails'.

When a new appointment record is added to the 'appointment' table, 300 records should be added to 'appointmentdetails'. What should happen is I take the 300 records in 'appointmentdetailstemplate', change the appointmentID on all 300 records, then insert these 300 changed records into 'appointmentdetails'.

So... to insert a new appointment into the database...
Add a single record to 'appointment'
Take 300 records from 'appointmentdetailstemplate' change the appointmentID for each record, then add all these modified records to 'appointmentdetails'.


This should be relatively simple, but I need to syntax for copying records out of one table, and inserting them into another. Any suggestions here would be very helpful.

Thank you!

 

[SQLSister]

First, how are you assigning the aapointmentID? Is tian identity field? If so a process simliar to the following should work

declare @ID as int
Insert into table1 (Field1, field2, field3)
Values (1, 'test', 'hi')
Select @id = scope_identity()

Insert into table2 (idfield, field1, field2, Field3)
Select @ID, b.field1, b.field2, b.field3 from table3 b

Questions about posting. See faq183-874

 

[nickmollberg]

Well, I need to add an appointment ID to each new record going from 'appointmentdetailstemplate' into 'appointmentdetails'

So, I guess I need some way to loop through the 'appointmentdetailstemplate' table and add every single record into the 'appointmentdetails' table.

 

[SQLSister]

No looping! Never loop, never even think about looping.

That's why I picked up the ID from a variable, it will insert the same id into all 300 records. If your AppointmentID is not an identity, you will have to add it to a variable some other way depending on how you create it, but the process I showed should work with tweaking for you particular application of course.



Questions about posting. See faq183-874

 

[nickmollberg]

I won't! Sorry, didn't know it was unwise.
So, how does the SQL query know to run through all 300 records? Is that what 'Select @id = scope_identity()' does?

For each new appointment, I need all 300 records to be created in detail.

 

[SQLSister]

Select @id etc grabs the value of the id field from the record inserted inthe first query. This only works if your ID field is an identity field.

The second query uses that id value in the insert and inserts all the records form table3 into table2 each one having the same id value which is tha value of the variable.

Questions about posting. See faq183-874