SQL insert records by duplicating based on counter

Question

If this is even possible, I need to insert/duplicate records based on what is indicated in the DuplicateNo field. to illustrate, if i have this table:

Table 'Customer':

CustNo      CustName       DuplicateNo
0001        John           2
0002        Smith          3

I need to have an output(new empty table) like this: Table 'DupliTable:

CustNo      CustName
0001        John
0001        John
0002        Smith
0002        Smith
0002        Smith

Thanks for the wonderful ideas people. I appreciate your help a lot.

there are no constraint. as long as i can get an output like what was illustrated. — dualdeath, Dec 01 '12 at 14:11

score 2 · Answer 1 · edited May 23 '17 at 12:27

2

In Access I think you only option is to write some code to do this. Here is a sample of iterating over records : Code to loop through all records in MS Access

edited May 23 '17 at 12:27

Community

1
1

answered Dec 01 '12 at 17:19

OneRealWinner

657
3
10

score 0 · Answer 2 · answered Dec 01 '12 at 17:19

OK, I think the easiest & fastest way would be to use multiple append queries using VBA. The code below runs an append query N times, where N = DuplicateNo, for each DuplicateNo and then filtering on the DuplicateNo field.

So it will run the append query once copying all the records where DuplicateNo = 1, run the query twice where DuplicateNo = 2, and so on.

The append query will run the sum of all unique DuplicateNo values. So, if the possible values are 1-4, then the query will run 10 times.

Sub FillOutputTable()
   Dim RS As Recordset
   Dim strSQL As String
   Dim N As Integer
   Dim I As Integer
   strSQL = ""
   strSQL = strSQL & "SELECT DuplicateNo "
   strSQL = strSQL & "FROM [table-input] "
   strSQL = strSQL & "GROUP BY [table-input].DuplicateNo "
   Set RS = CurrentDb.OpenRecordset(strSQL)
   Do
      N = RS("DuplicateNo")
      For I = 1 To N
         strSQL = ""
         strSQL = strSQL & "INSERT INTO [table-output] ( CustNo, CustName ) "
         strSQL = strSQL & "SELECT [table-input].CustNo, [table-input].CustName "
         strSQL = strSQL & "FROM [table-input] "
         strSQL = strSQL & "WHERE [table-input].DuplicateNo=" & N
         CurrentDb.Execute strSQL
      Next I
      RS.MoveNext
   Loop Until RS.EOF
   RS.Close
   Set RS = Nothing
End Sub

SQL insert records by duplicating based on counter

2 Answers2