lundi 15 février 2016

Insert multiple rows avoiding cursors and loops in SQL Server

I'm trying to insert multiple rows that correspond to each payment that several clients have to do in a given date.
Obviously there are 2 tables, the first being the clients table:

cust_id, name ...
1, John Doe
2, Jane Smith
...

And the second being the payments table, using a serialized payment_id and incremental dates by 1 month for each payment: (ID is an identity column)

ID, cust_id, payment_id, due_date
1, 1, 1, 2016-01-01
2, 1, 2, 2016-02-01
3, 1, 3, 2016-03-01
5, 2, 1, 2016-01-01
6, 2, 2, 2016-02-01
7, 2, 3, 2016-03-01
...

I've seen that using WHILE loops should be avoided as stated in this answer, and cursors would take a long time if we are speaking of thousands of clients and tens of payments for each one.

Any pointer would help, thanks.

-- Edited to correct a typo in the title.

Aucun commentaire:

Enregistrer un commentaire