By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,375 Members | 3,001 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,375 IT Pros & Developers. It's quick & easy.

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

P: n/a
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.TOTALNUMBER

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.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPathBase & ";"

Set conn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
rs1.Open "SELECT * FROM Table1 ", conn, adOpenStatic, adLockOptimistic
rs1.movefirst
Do while not rs1.EOF

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

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

Thank you.

Mar 9 '07 #1
Share this Question
Share on Google+
4 Replies


P: n/a
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.googlegr oups.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.TOTALNUMBER

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.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPathBase & ";"

Set conn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
rs1.Open "SELECT * FROM Table1 ", conn, adOpenStatic, adLockOptimistic
rs1.movefirst
Do while not rs1.EOF

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

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

Thank you.

Mar 10 '07 #2

P: n/a
On 10 mar, 06:28, "Larry Linson" <boun...@localhost.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.

Mar 14 '07 #3

P: n/a
ca******@gmail.com wrote in news:1173469137.480177.256350
@v33g2000cwv.googlegroups.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.TOTALNUMBER

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.ConnectionString = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=" & sPathBase & ";"

Set conn = New ADODB.Connection
Set rs1 = New ADODB.Recordset
rs1.Open "SELECT * FROM Table1 ", conn, adOpenStatic, adLockOptimistic
rs1.movefirst
Do while not rs1.EOF

id = rs1("ID")
description= rs1("DESCRIPTION")
sql="INSERT INTO Table2 VALUES (" & id & ",'" & description & "')"
conn.execute(sql)
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.

--
Mar 14 '07 #4

P: n/a
Thank you lyle

Mar 15 '07 #5

This discussion thread is closed

Replies have been disabled for this discussion.