Mass recorded adding

[nbcrockett]

What's the best way to take the contents from a table in one database and dump it into another table in another database?

[russell]

depends. what dbms? also depends if many records (hundred of thousands+) or few...

[nbcrockett]

We've needed to do as little as 1 to as many as 25000. We're using Access.

[russell]

easiest to use export/import in msaccess. if u MUST use ASP, this will work.

<%
main

Sub main()
Dim ar
ar = getOriginalData()

If isArray(ar) Then
Upload ar
Response.Write "ok"
Else
Response.Write "No Data"
End If
End Sub

Sub Upload(ar)
Dim i
Dim sql
Dim cmd

Set cmd = Server.CreateObject("ADODB.Command")

'' need to declare variables and assign them here
'' for values to be inserted into db. for example, if our select statement
'' was select id, name, address from originalTable:

id = ar(0, i)
name = ar(1, i)
address = ar(2, i)

With cmd
.ActiveConnection = ConnStringForNewMDB
.CommandType = 1
.CommandText = sql

For i = 0 to ubound(ar, 2)
sql = "INSERT INTO newTable (id, name, address) " &_
"Values (" & id & ", '" & name & "', '" & address & "')"

.Execute
Next
End With

Set cmd = Nothing
End Sub

Function getOriginalData()
Dim cmd
Dim rs
Dim sql

sql = "SELECT id, name, address FROM originalTable"

Set cmd = Server.CreateObject("ADODB.Command")
Set rs = Server.CreateObject("ADODB.Recordset")

With cmd
.ActiveConnection = ConnStringForOriginalMDB
.CommandType = 1
.CommandText = sql

rs.Open .Execute
End With

If Not rs.EOF Then
getOriginalData = rs.GetRows
rs.Close
End If

Set rs = Nothing
Set cmd = Nothing
End Function
%>

[nbcrockett]

I was thinking about doing a loop which is basically what you did. Thanks!