1

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.

Community
  • 1
  • 1
M. G.
  • 13
  • 4
  • 1
    Insert from what? An application? What's that application written in? Another table? Another query? Normally I would suggest Table Valued Parameters, but those were not introduced until SQL Server 2008. Server 2005 is *very* old at this point. – Bacon Bits Feb 15 '16 at 16:29
  • Sorry for not being more specific. I'm tying to write an stored procedure for inserting rows in a new payments table, using an existing clients table. Each client has to make 48 fixed payments each month. Regarding SQL server 2005, this is what my current employers have at his moment. They will, however, upgrade to a newer version, but not at this time, so I have to work with what is available, but if you have a way to do it in 2008, it will help me in the future. Thanks. – M. G. Feb 15 '16 at 16:51
  • If you don't have table valued parameters you can use XML types to transfer sets of data... but if you upgrade to at least 2008 you can use table value parameters and merge statements. – Matthew Whited Feb 15 '16 at 17:14
  • Can you give an example schema of what this new payments looks like? – Mazhar Feb 17 '16 at 09:54
  • So, in order to conclude this question, it is not possible to do it in SQL Server 2005; in SLQS 2008 it can be done using Table Valued Parameters. – M. G. Feb 18 '16 at 16:21
  • I'd like to point to a [comment](http://stackoverflow.com/a/15182759/4829914) in wich states that loops can be used "occasionally as a one time thing to populate a table", wich is my case. I was about to answer my own question, but I guess all credits should go to @Bacon Bits. – M. G. Feb 18 '16 at 16:29
  • 1
    From an application, no, not really. About the best you can do in .Net is populate a DataTable and push it up through an SQLBulkCopy. Historically people would send parameters to stored procs in comma delimited lists or in XML, but that's really gross. Honestly, though it almost sounds like you could write a query that, given the start date, could generate all the payment dates. You could write a stored procedure to do that pretty easily, but it's hard to tell due to the lack of information. – Bacon Bits Feb 18 '16 at 19:40

0 Answers0