Solved

INSERT array at once into SQL server ??

Posted on 2008-10-24
16
2,088 Views
Last Modified: 2008-11-05
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
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

Open in new window

0
Comment
Question by:Schuttend
  • 6
  • 5
  • 4
  • +1
16 Comments
 
LVL 2

Expert Comment

by:Clausewitz
ID: 22798873
you can put all your rows you like to enter into a single SQL-query, like

INSERT INTO dbo.[Data] ([Date],Signal,Equity) values ('Date1','Signal1,'Equity1'), ('Date2','Signal2,'Equity2'), ('Date3','Signal3,'Equity3')
0
 

Author Comment

by:Schuttend
ID: 22798885
Hi,

I have more then 100.000 values, this is just not possible....
0
 
LVL 2

Expert Comment

by:Clausewitz
ID: 22798939
is serializing the data to a csv file and using bulk insert an option?
0
 

Author Comment

by:Schuttend
ID: 22798957
I have never heard about this, but maybe it is. Can you tell me more about this?
0
 
LVL 16

Expert Comment

by:HooKooDooKu
ID: 22798997
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.
0
 
LVL 2

Expert Comment

by:Clausewitz
ID: 22798996
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
0
 
LVL 2

Expert Comment

by:Clausewitz
ID: 22799038
@HooKooDooKu:
you're right. Indexes might slow it down too. But i think the single 100'000 SQL-queries are the real bottleneck here.
0
 

Author Comment

by:Schuttend
ID: 22799059
Hi,

Thank for you that. But I need it to be programmed from within visual basic 6. Sorry for the confusion...
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 2

Expert Comment

by:Clausewitz
ID: 22800340
i don't know much about VB6, but i don't think inserting multiple rows will be possible with it.
0
 
LVL 22

Expert Comment

by:dportas
ID: 22802282
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.
0
 

Author Comment

by:Schuttend
ID: 22804033
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....
0
 
LVL 22

Expert Comment

by:dportas
ID: 22804243
0
 

Author Comment

by:Schuttend
ID: 22806630
Is there any way to do this from within vb6??

Regards,
Dennis
0
 
LVL 22

Expert Comment

by:dportas
ID: 22806645
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.
0
 

Author Comment

by:Schuttend
ID: 22806655
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.
0
 
LVL 22

Accepted Solution

by:
dportas earned 250 total points
ID: 22806670
The process is the same as for any other SQL command executed from ADO: Create a connection. Create the command object. Set the command text and call the Execute method. I can't find an example right now but if you consult the Help file or Google for ADO VB6 you'll find plenty of examples.

It's good practice to do all data access in SQL Server using stored procedures. So consider creating a SQL stored procedure to do the actual INSERT and then calling that from your VB code.

To use BULK INSERT the source file must be accessible to the server, must be specified using a physical drive letter or UNC path (not mapped drives) and the SQL service account must have permission to read that path and file.
0

Featured Post

6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

Join & Write a Comment

In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
This lesson covers basic error handling code in Microsoft Excel using VBA. This is the first lesson in a 3-part series that uses code to loop through an Excel spreadsheet in VBA and then fix errors, taking advantage of error handling code. This l…

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now