Adding records to a table based on the value of one field of other

[cacanene]

My question is what will be the fast algorithm to add records in a
table based on the value of a field of other table.

For example:
TABLE2 contains two fields ID and DESCRIPTION
TABLE1 contains three fields ID, DESCRIPTION and TOTALNUMBER
Then, how to create so many rows in TABLE2 as the value of
TABLE1.TOTALNUM BER

If
TABLE1
------------
ID DESCRIPTION TOTALNUMBER
1 apples 5
2 oranges 4

then TABLE2
--------------------
ID DESCRIPTION
1 apples
1 apples
1 apples
1 apples
1 apples
2 oranges
2 oranges
2 oranges
2 oranges

I propose one, however I'm not sure if it is the fastest way (even if
it is fine).

--------------------------- CODE
-------------------------------------------------------------
conn.Connection String = _
"Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & sPathBase & ";"

Set conn = New ADODB.Connectio n
Set rs1 = New ADODB.Recordset
rs1.Open "SELECT * FROM Table1 ", conn, adOpenStatic, adLockOptimisti c
rs1.movefirst
Do while not rs1.EOF

id = rs1("ID")
description= rs1("DESCRIPTIO N")
sql="INSERT INTO Table2 VALUES (" & id & ",'" & description & "')"
conn.execute(sq l)
rs1.movenext
Loop

set conn = nothing
------------------------ END CODE
------------------------------------------------------------------

Thank you.

[Larry Linson]

I'm not even sure what valid purpose would be served by doing so. From your
example, it seems to be the work of the Department of Redundancy
Department -- that is, creating a number of redundant records. But, then,
there might be a purpose in "real life" when you aren't obscuring it with
"sample data".

Larry Linson
Microsoft Access MVP
<ca******@gmail .comwrote in message
news:11******** **************@ v33g2000cwv.goo glegroups.com.. .

My question is what will be the fast algorithm to add records in a
table based on the value of a field of other table.

For example:
TABLE2 contains two fields ID and DESCRIPTION
TABLE1 contains three fields ID, DESCRIPTION and TOTALNUMBER
Then, how to create so many rows in TABLE2 as the value of
TABLE1.TOTALNUM BER

If
TABLE1
------------
ID DESCRIPTION TOTALNUMBER
1 apples 5
2 oranges 4

then TABLE2
--------------------
ID DESCRIPTION
1 apples
1 apples
1 apples
1 apples
1 apples
2 oranges
2 oranges
2 oranges
2 oranges

I propose one, however I'm not sure if it is the fastest way (even if
it is fine).

--------------------------- CODE
-------------------------------------------------------------
conn.Connection String = _
"Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & sPathBase & ";"

Set conn = New ADODB.Connectio n
Set rs1 = New ADODB.Recordset
rs1.Open "SELECT * FROM Table1 ", conn, adOpenStatic, adLockOptimisti c
rs1.movefirst
Do while not rs1.EOF

id = rs1("ID")
description= rs1("DESCRIPTIO N")
sql="INSERT INTO Table2 VALUES (" & id & ",'" & description & "')"
conn.execute(sq l)
rs1.movenext
Loop

set conn = nothing
------------------------ END CODE
------------------------------------------------------------------

Thank you.

[CNeng]

On 10 mar, 06:28, "Larry Linson" <boun...@localh ost.notwrote:

I'm not even sure what valid purpose would be served by doing so. From your
example, it seems to be the work of the Department of Redundancy
Department -- that is, creating a number of redundant records. But, then,
there might be a purpose in "real life" when you aren't obscuring it with
"sample data".

Larry Linson
Microsoft Access MVP

Your answer was very helpful,
now I realize why you are MVP ;)

Of course there is a "real life" purpose, however it is not the main
issue in this thread (and forum).
Thank you again.

[lyle fairfield]

ca******@gmail. com wrote in news:1173469137 .480177.256350
@v33g2000cwv.go oglegroups.com:

My question is what will be the fast algorithm to add records in a
table based on the value of a field of other table.

For example:
TABLE2 contains two fields ID and DESCRIPTION
TABLE1 contains three fields ID, DESCRIPTION and TOTALNUMBER
Then, how to create so many rows in TABLE2 as the value of
TABLE1.TOTALNUM BER

If
TABLE1
------------
ID DESCRIPTION TOTALNUMBER
1 apples 5
2 oranges 4

then TABLE2
--------------------
ID DESCRIPTION
1 apples
1 apples
1 apples
1 apples
1 apples
2 oranges
2 oranges
2 oranges
2 oranges

I propose one, however I'm not sure if it is the fastest way (even if
it is fine).

--------------------------- CODE
-------------------------------------------------------------
conn.Connection String = _
"Provider=Micro soft.Jet.OLEDB. 4.0;" & _
"Data Source=" & sPathBase & ";"

Set conn = New ADODB.Connectio n
Set rs1 = New ADODB.Recordset
rs1.Open "SELECT * FROM Table1 ", conn, adOpenStatic, adLockOptimisti c
rs1.movefirst
Do while not rs1.EOF

id = rs1("ID")
description= rs1("DESCRIPTIO N")
sql="INSERT INTO Table2 VALUES (" & id & ",'" & description & "')"
conn.execute(sq l)
rs1.movenext
Loop

set conn = nothing
------------------------ END CODE
------------------------------------------------------------------

Thank you.

I think the fastest way is likely to be to create a DAO recordset of
Table2 records and to use AddNew and Update in keeping with the values
found in Table1. In my experience, this is a case in which DAO is not
just fast, but blows the sox off ADO and/or JET SQL. If there are a bunch
of new records to create I'd guess this way might be ten or more times
faster than the fastest other way.
BTW, I use ADO wherever DAO or SQL does not have a clear advantage; in
this case I think the advantage is overwhelming.

--

[CNeng]

Thank you lyle