How to duplicate records in SQL output

Question

My question is identical to the one in How can SQL create duplicate records?. However, I'm trying to do it in Access, and none of the solutions there appear to be applicable. Preferably, I'd like a single SQL statement.

Thanks.

Answer 1

The accepted answer from that linked question can be adapted for Access. Create your num table from the user interface. If you want to create the table with a DDL statement, use this.

Dim strDdl As String
strDdl = "CREATE TABLE num (" & vbCrLf & _
    "i INTEGER PRIMARY KEY" & vbCrLf & _
    ");"
Debug.Print strDdl
CurrentProject.Connection.Execute strDdl

Load the num table. In Access, a VBA procedure would be convenient.

Public Sub Load_Num_table()
    Dim db As DAO.Database
    Dim rs As DAO.Recordset
    Dim x As Long

    Set db = CurrentDb
    Set rs = db.OpenRecordset("num", dbOpenTable, dbAppendOnly)
    For x = 1 To 100
        rs.AddNew
        rs!i = x
        rs.Update
    Next x
    rs.Close
    Set rs = Nothing
    Set db = Nothing
End Sub

With your num table prepared and your other table named tableX, use this SQL statement to return a row set with duplicated rows.

SELECT t.id, t.name
FROM
    tableX AS t
    INNER JOIN num 
    ON num.i <= t.count;

The only real difference in that SELECT statement vs. the one suggested by @ypercube in the accepted answer to that other question is INNER JOIN instead of just JOIN. Access' db engine won't accept JOIN alone; you must specify the join type (inner, left, or right).

Answer 2

Insert Into TableName (field 1, field 2, ...) Select * From TableName;

if u have primary key will give error.