Schuttend
asked on
INSERT array at once into SQL server ??
Hello,
I have the attached code. Is there a way to put the array into the table at once without looping through it?
The attached code takes much too long to process.
Regards,
Dennis
I have the attached code. Is there a way to put the array into the table at once without looping through it?
The attached code takes much too long to process.
Regards,
Dennis
Function Add_Record1()
'Dim cn As ADODB.Connection
Dim rs As Recordset
'Set cn = New ADODB.Connection
Dim name
name = Text3.Text
Dim query
For y = 1 To numberofsignals
For i = 1 To numberofrows
If i - Tradingdelay >= 1 Then
If close_price(i) <> "" And signal(i - Tradingdelay, y) <> "" And close_price(i - Tradingdelay) <> "" Then
query = "INSERT INTO " & name & ".dbo.[Data] ([Date],Signal,Equity) values ('" & datum(i) & "','" & signal(i, y) & "','" & equity(i, y) & "')"
Set rs = ado.Execute(query)
End If
End If
Next
Next
Debug.Print ("Processingnr:" & processingnumber)
End Function
ASKER
Hi,
I have more then 100.000 values, this is just not possible....
I have more then 100.000 values, this is just not possible....
is serializing the data to a csv file and using bulk insert an option?
ASKER
I have never heard about this, but maybe it is. Can you tell me more about this?
Well, you have to either insert with a single query or insert one at a time.
However, the thing that might be slowing you down is updating database indexes. With each insert call, the database has to update all indexes for the table for which you are inserting records. Record inserts go much faster on a table without indexes.
A few ideas:
1. Drop the indexes from the table you are updating, insert all the records, then reapply the indexes (bad idea if you already have way more records in the database compared to the number of records being added).
2. Create a duplicate table in the database, but don't include any indexes. Insert all of the records into the duplicate table. Then with a single query, copy the records from the duplicate table to the main table.
3. If this is a new database being constructed on the fly, add indexes AFTER all the records have been inserted into the table.
However, the thing that might be slowing you down is updating database indexes. With each insert call, the database has to update all indexes for the table for which you are inserting records. Record inserts go much faster on a table without indexes.
A few ideas:
1. Drop the indexes from the table you are updating, insert all the records, then reapply the indexes (bad idea if you already have way more records in the database compared to the number of records being added).
2. Create a duplicate table in the database, but don't include any indexes. Insert all of the records into the duplicate table. Then with a single query, copy the records from the duplicate table to the main table.
3. If this is a new database being constructed on the fly, add indexes AFTER all the records have been inserted into the table.
you find a simple example here: http://blog.sqlauthority.com/2008/02/06/sql-server-import-csv-file-into-sql-server-using-bulk-insert-load-comma-delimited-file-into-sql-server/
basicly you send a file with all the rows to sql server and tell it how the data is structured.
You find more detailed information here: http://msdn.microsoft.com/en-us/library/ms188365.aspx
basicly you send a file with all the rows to sql server and tell it how the data is structured.
You find more detailed information here: http://msdn.microsoft.com/en-us/library/ms188365.aspx
@HooKooDooKu:
you're right. Indexes might slow it down too. But i think the single 100'000 SQL-queries are the real bottleneck here.
you're right. Indexes might slow it down too. But i think the single 100'000 SQL-queries are the real bottleneck here.
ASKER
Hi,
Thank for you that. But I need it to be programmed from within visual basic 6. Sorry for the confusion...
Thank for you that. But I need it to be programmed from within visual basic 6. Sorry for the confusion...
i don't know much about VB6, but i don't think inserting multiple rows will be possible with it.
What is the source for the data? Depending on the source their may be different solutions. For example if it's a file you could use BCP or SSIS. If it's a query then you could insert it without having to loop through each row.
ASKER
The source is an array of data, but I can make it a file as well.
Could you please give me an example how to insert a array at once? It's indeed a query....
Could you please give me an example how to insert a array at once? It's indeed a query....
ASKER
Is there any way to do this from within vb6??
Regards,
Dennis
Regards,
Dennis
Yes. You can execute any of those T-SQL statements (BULK INSERT or OPENROWSET) from VB6 using ADO. ADO is the standard way to interface with SQL Server.
However, VB6 is effectively obsolete and has been de-supported for years. It normally shouldn't be used for new projects. Certain features won't be supported. For example in SQL Server 2008 you have the option of passing arrays of values using table-valued parameters. I don't think you'll be able to do that in VB6, although I haven't tried it myself from VB6 so I can't be absolutely sure.
However, VB6 is effectively obsolete and has been de-supported for years. It normally shouldn't be used for new projects. Certain features won't be supported. For example in SQL Server 2008 you have the option of passing arrays of values using table-valued parameters. I don't think you'll be able to do that in VB6, although I haven't tried it myself from VB6 so I can't be absolutely sure.
ASKER
Could you please give me an example of BULK INSERt in vb6 code? I am working with ADO in my current project, so this should work I guess.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
INSERT INTO dbo.[Data] ([Date],Signal,Equity) values ('Date1','Signal1,'Equity1