/    Sign up×
Community /Pin to ProfileBookmark

Mass recorded adding

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

to post a comment

4 Comments(s)

Copy linkTweet thisAlerts:
@russellDec 11.2006 — depends. what dbms? also depends if many records (hundred of thousands+) or few...
Copy linkTweet thisAlerts:
@nbcrockettauthorDec 11.2006 — We've needed to do as little as 1 to as many as 25000. We're using Access.
Copy linkTweet thisAlerts:
@russellDec 11.2006 — easiest to use export/import in msaccess. if u MUST use ASP, this will work.
<i>
</i>&lt;%
main

Sub main()
Dim ar
ar = getOriginalData()

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

Sub Upload(ar)
Dim i
Dim sql
Dim cmd

<i> </i>Set cmd = Server.CreateObject("ADODB.Command")
<i> </i>[color=green]
<i> </i>'' need to declare variables and assign them here
<i> </i>'' for values to be inserted into db. for example, if our select statement
<i> </i>'' was select id, name, address from originalTable:
<i> </i>[/color]
<i> </i>id = ar(0, i)
<i> </i>name = ar(1, i)
<i> </i>address = ar(2, i)

<i> </i>With cmd
<i> </i> .ActiveConnection = ConnStringForNewMDB
<i> </i> .CommandType = 1
<i> </i> .CommandText = sql

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

<i> </i> .Execute
<i> </i> Next
<i> </i>End With

<i> </i>Set cmd = Nothing
End Sub

Function getOriginalData()
Dim cmd
Dim rs
Dim sql

<i> </i>sql = "SELECT id, name, address FROM originalTable"

<i> </i>Set cmd = Server.CreateObject("ADODB.Command")
<i> </i>Set rs = Server.CreateObject("ADODB.Recordset")

<i> </i>With cmd
<i> </i> .ActiveConnection = ConnStringForOriginalMDB
<i> </i> .CommandType = 1
<i> </i> .CommandText = sql

<i> </i> rs.Open .Execute
<i> </i>End With

<i> </i>If Not rs.EOF Then
<i> </i> getOriginalData = rs.GetRows
<i> </i> rs.Close
<i> </i>End If

<i> </i>Set rs = Nothing
<i> </i>Set cmd = Nothing
End Function
%&gt;
Copy linkTweet thisAlerts:
@nbcrockettauthorDec 11.2006 — I was thinking about doing a loop which is basically what you did. Thanks!
×

Success!

Help @nbcrockett spread the word by sharing this article on Twitter...

Tweet This
Sign in
Forgot password?
Sign in with TwitchSign in with GithubCreate Account
about: ({
version: 0.1.9 BETA 2.28,
whats_new: community page,
up_next: more Davinci•003 tasks,
coming_soon: events calendar,
social: @webDeveloperHQ
});

legal: ({
terms: of use,
privacy: policy
});
changelog: (
version: 0.1.9,
notes: added community page

version: 0.1.8,
notes: added Davinci•003

version: 0.1.7,
notes: upvote answers to bounties

version: 0.1.6,
notes: article editor refresh
)...
recent_tips: (
tipper: @Samric24,
tipped: article
amount: 1000 SATS,

tipper: Anonymous,
tipped: article
amount: 10 SATS,

tipper: @neststayhome,
tipped: article
amount: 1000 SATS,
)...