How can i copy data from one database to another

**dace64** · Dec 20, 2012 06:12

For example
db1.mdb copy everything from a filed to database db2.

Im newbee and nedd how to setup the connectionbetween them too

**gk53** · Dec 21, 2012 13:47

in simple case ( i assume you on the same server)

insert into db2.dbo.Table1
select * from db1.dbo.table1

**dace64** · Dec 21, 2012 14:06

yes it should be simple btw thx for the answer
My script looks like this ... it works fine but it only copy the first record from the first databese to the second database

<%

Set Conn1=Server.CreateObject("ADODB.Connection")
conn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath ("/scriptarkiv/nopho_conn/database/nopho_01.mdb")
set rs = conn1.execute("Select Ballot_emailgroup FROM nophousers")
tempfield1 = rs("Ballot_emailgroup")
rs.close
set rs= nothing
conn1.close
set conn1 = nothing

set conn2=Server.CreateObject("ADODB.Connection")
conn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath ("/member_pages/ballot/vote.mdb")
conn2.execute "Insert into data (email) Values ('" & tempfield1 & "')"

%>

**gk53** · Dec 21, 2012 14:47

In this case you need to modify you code

<%

Set Conn1=Server.CreateObject("ADODB.Connection")
conn1.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath ("/scriptarkiv/nopho_conn/database/nopho_01.mdb")

set conn2=Server.CreateObject("ADODB.Connection")
conn2.Open "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & server.MapPath ("/member_pages/ballot/vote.mdb")

set rs = conn1.execute("Select Ballot_emailgroup FROM nophousers")
do while not rs.EOF

conn2.execute "Insert into data (email) Values ('" & rs("Ballot_emailgroup") & "')"

rs.MoveNext
loop

rs.close
set rs= nothing
conn1.close
set conn1 = nothing

%>

**dace64** · Dec 21, 2012 15:13

Thx gk53. realy appreciate your help
Now it works fine

**dace64** · Jan 14, 2013 15:22

And if i want som kind off errorhandler to the same script, if empty record move to next record without stop.
Is that posible

**dace64** · Jan 14, 2013 15:39

Sorry should be in the script below ... the errorhandler

<%
Dim rs, mail, key, subject, AddAttachment, message, data_source, sql_select, no
server.scriptTimeout=260

Sub Pause(intSeconds)
startTime = Time()
Do Until DateDiff("s", startTime, Time(), 0, 0) > intSeconds
Loop
End Sub

no = 0

mailcount=0

sql_select = "select * from data"
data_source = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & _
Server.MapPath("/member_pages/ballot/vote.mdb")
If Len(message) Then
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open sql_select, data_source

While Not rs.EOF
Set abMail = Server.CreateObject ("CDO.message")
set cdoConfig = Server.CreateObject("CDO.Configuration")
cdoConfig.Fields("http://schemas.microsoft.com/cdo/configuration/sendusing") = 2
cdoConfig.Fields("http://schemas.microsoft.com/cdo/configuration/smtpserver") = "mail-hub.space.com"
cdoConfig.Fields.Update
set abMail.Configuration = cdoConfig

abMail.From = "admin@admin.org"
abMail.To = rs("email")
abMail.Subject = "Subject"
abMail.htmlBody = "<html><body> your Password: "& rs("VoteKey") &" </body></html>"
' abMail.AddAttachment Server.MapPath("ballot.pdf")
abMail.Send
Set abMail= Nothing

Response.Write "Email sent to : " & rs("email") & " "
no = no + 1
mailcount = mailcount + 1
If mailcount = 100 Then
pause(5)
mailcount = 0
End If
rs.MoveNext
Wend

Server.ScriptTimeout = TimeOutVar
Response.Write "Emails sent to " & no & " users."
rs.Close
Set rs = Nothing
Else
Response.Redirect "somesite.htm"
End If
%>

User Tag List

Thread: How can i copy data from one database to another

Thread Tools

Display

How can i copy data from one database to another

Bookmarks

Bookmarks

Posting Permissions