Inserting into db from getrows array

Results 1 to 2 of 2

Thread: Inserting into db from getrows array

  1. #1
    Join Date
    Dec 1969

    Default Inserting into db from getrows array

    I&#039;m trying to figure out how to insert a lot of information into a SQL Server table from another database without it taking so long.<BR><BR>As of now, I basically connect to my destination database and connect to my source database at the same time and create a recordset from my source database. I then make all the variables in the recordset local, change them, then insert them back into the second database.<BR><BR>So I have code like:<BR> new connection (SourceDB)<BR>cybrs new recordset (SourceDB)<BR> new connection (DestDB)<BR>cybrs.Open "Select * from OldTable", cybconn<BR><BR>DO UNTIL cybrs.eof<BR>sql = "INSERT INTO NewTable Values(&#039;" & a & "&#039;,&#039;" & b & "...)"<BR>conn.execute sql<BR>cybrs.movenext<BR>LOOP<BR>cybrs.close<BR>Se t cybrs = Nothing<BR>conn.Close<BR>cybconn.close<BR>------<BR><BR>Which runs painfully slow (45 seconds for a table with 4k rows and 20 columns.)<BR><BR>So next thing, I tried to use getrows. So my code turned into something like:<BR>sourcedb.connect<BR>sql = select * from table<BR>p = getrows()<BR>close connection<BR><BR>open new connection destinationdb<BR>for i=x to ubound(p)<BR>insert sql<BR>next<BR>------<BR><BR>So now I need to turn my array into a list where I can insert it without having to concatenate 20 times for each row, I think that&#039;s where the bulk of my speed problem was.<BR>Here&#039;s my speed results with getrows<BR>Total Time: 29082<BR>Time after delete: 6370<BR>Time to fix array: 330<BR>Time to insert: 22382 <BR><BR>So I guess my question is, how do I insert my array (p) into a SQL database.<BR>[code language="VBScript"]<BR>FOR i=iRecStart to iRecEnd<BR>sql = "INSERT INTO EmployeeCyborg Values(" & p(i) & ")"<BR>conn.execute sql<BR>NEXT<BR>[/code]<BR>doesn&#039;t work... is there a way to like extract one dimension from a 2d array?

  2. #2
    Join Date
    Dec 1969

    Default Do it ALL in SQL!

    INSERT INTO NewTable <BR>SELECT a,b FROM OldTable<BR><BR>Presto.<BR><BR>And yes, you *can* use an "OldTable" that is in another database, even, with SQL Server. Any ODBC compliant database, in fact.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts