saving table data

[Smee]

is there a way to save a table to a database faster than this. once the records start to add up this method becomes very slow


For i = 0 To sTblOrders.RowCount-1
Timex=sTblOrders.Cell("OrderTime",i)
ProdNo=sTblOrders.Cell("ProductCode",i)
ProdPrice=sTblOrders.Cell("ProductPrice",i)
Qty=sTblOrders.Cell("Qty",i)
'add row to database
ItemNo =i
txt= "('" & VanNo & "','" & CustCode & "','" & TrxNo & "','" & ItemNo _
& "','" & Timex & "','" & ProdNo & "','" & ProdPrice & "','" & Qty & "')"
command.CommandText = "INSERT INTO Sales VALUES " & txt
Command.ExecuteNonQuery
Next

Still Learning
:sign0104:

 

[Erel]

Yes. First you can use Connection.StartTransaction and Connection.EndTransaction before and after the loop to increase the performance.
However a simpler solution is to use Connection.CreateSQLTable.

 

[Smee]

Thank you Erel,

However Connection.CreateSQLTable. would not work because the Table already exists and the data is an update of an existing table. Is there an append command similar to this?

Thanks

Joe

 

[Erel]

You can use CreateSQLTable to create a temporary table.
Then issue a command like:

INSERT INTO Sales SELECT * from TemporaryTable

 

[Smee]

Thank you Erel

i will try this