hi Duncan,

It depends on how many records you want to retrieve from the database.. If
you want to do mass insert such as taking from a temporary table or file and
inserting to a table, it might be faster to use store procedures.

Here is an example of how you can call a store procedure from VB, and a
store procedure in SQL..  this example takes data from a temporary table and
inserting it into the real table.  I have bout 10,000 records in the temp
table, and it took about 5-10 seconds.

take a look at it and see if it will help in anyway.

amanda.





====
vb
====
Public Sub AllTab_action()

Dim lcn As New ADODB.Connection
Dim lcmd As ADODB.Command
lcn.ConnectionTimeout = 0
lcn.Open strConnect

Set lcmd = New ADODB.Command
Set lcmd.ActiveConnection = lcn
lcmd.CommandTimeout = 0


        lcmd.CommandType = adCmdStoredProc
        lcmd.CommandText = "sp_InsertInvoice"
        lcmd.Execute

  Set lcmd = Nothing
  lcn.Close
  Set lcn = Nothing

End Sub

*****
sql.

*****



if exists (select * from sysobjects where id 
object_id('dbo.sp_InsertInvoice') and sysstat & 0xf = 4)
	drop procedure dbo.sp_InsertInvoice
GO

CREATE PROCEDURE sp_InsertInvoice AS


Begin transaction trans
 insert into invoice (invoicenumber,
accountnumber,companyname,productionmonth,
                     rundate,invoicetotal)
(select invoicenumber,
max(accountnumber),max(companyname),max(productionmonth),
         max(convert(datetime(8), rundate,11 )),max(invoicetotal) from
temp_inv
 where not exists(select 1 from invoice where invoice.invoicenumber 
temp_inv.invoicenumber)
 group by invoiceNumber)

COMMIT transaction

Dump transaction gmir with no_log
GO


-----Original Message-----
From: Brandon Duncan [mailto:bduncan@p...]
Sent: June 23, 2000 4:00 PM
To: professional vb
Subject: [pro_vb] Fastest Way to Get Data into a Database


I'm using ADO in VB6.  I have to be able to interface to several different
databases (SQL Server, ORACLE, ect).  Does anyone know the fastest way to
Get records into a database?  I've heard stored procedures are fast, but
the only examples I've ever seen are for retrieving recordsets.  I need to
execute a variable number of dynamically created INSERT statements.  Can
you do this in stored procedures?  If so, how?  If not, what is the fastest
way to get the data into the database?

Thanks for any insights,

Brandon