Mass recorded adding

@russellDec 11.2006 — #depends. what dbms? also depends if many records (hundred of thousands+) or few...

@nbcrockettauthorDec 11.2006 — #We've needed to do as little as 1 to as many as 25000. We're using Access.

@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;

@nbcrockettauthorDec 11.2006 — #I was thinking about doing a loop which is basically what you did. Thanks!

Mass recorded adding

4 Comments(s) _↴

Success!

Social

Version

Mass recorded adding

4 Comments(s) ↴

Success!

The web is an endless sea of information. Don't miss the boat... Subscribe!

Social

Version

4 Comments(s) _↴