Macro Design For Bulk Insert Into

[gkick]

Win 10 LO 6.4.6.2 HSQL 2.51

Hi,

tblRentalObject rid (PK) rentalObject Varchar
tblBookings bid PK rid (FK) rentaldate Date

After a new RentalObject record is created, I want to run an insert statement to insert one year worth of records
into the bookings table starting with the current date increment by 1.
How would I set up the loop or is there a more efficient way ?

On db startup I also want to run another insert statement to add 1 day after the last one for every RentalObect present in bookings.
Guess its another loop but more complex as the last dates of rentalobjects may vary based on creationdate.

Grateful for any pointers, thanks

[Ratslinger]

Hello,

From my view, that is the opposite way to go. I would only create records when there is an actual rental and then one for the entire rental - not individual days. Your method will most likely result in a lot of unused records wasting space and resources plus some undefined future headaches.

[gkick]

@Ratslinger

First of all I would like to thank you for all your help over the year and wish you and your loved ones a safe and Merry Christmas.

Generally agree with your view. There is a German sample database which has a reservation table where one field is a date and the remaining fields are the rental objects. The whole thing does magic via sql and views.
Trouble with the design is one can not add additional objects in a dynamic way.(Previous question on query)
I need some way and this is just conceptual thinking for now, to select a rental object in a form, specify a date range and display only dates which have not yet been booked, sort of a calendaring thing.

[Ratslinger]

@gkick,

Glad to have been of some help. And Merry Christmas to you and all your family.

You comment is somewhat like my thinking. If your looking for availability, display something of a calendar image (can use a text table), SQL to get dates (these are booked dates), and fill table with available then mark the booked dates.

It does appear you need to spend some time mulling all this over. See this too many times with others - build as you go. Should be design and re-design until all is well. Then build.