Solved

VB6 - Updating SQL table from a non-SQL connection

Posted on 2011-09-02
8
336 Views
Last Modified: 2012-05-12
Hello Experts,

I have a SQL database that I want to update with non-SQL information via an ODBC link.  The link to the SQL database works, as well as the non-SQL link--they both work fine using something akin to the attached code (don't hold me to this--I distilled this from a larger spread of code, and I may be missing a dim or a set here or there, but the concept is what I am trying to get across--my originating code works).  So here's the issue:  If the client has, say, 50,000 records in their view, I don't want to have to hit SQL with 50,000 UPDATE commands.  If both of my tables were native to SQL, I would like to do something like:  UPDATE SqlTable Set Address = ClientTable.Address FROM SqlTable JOIN ClientTable ON SqlTable.ClientID = ClientTable.CustNo.  However, they're not, and I have the attached code which is dog slow.

So. . .  Any ideas?  The data I'm updating is for a large accounting product--I'm not sure if i can do a SQL stored procedure, or even if it would work, but I can do a temporary SQL table; however, I imagine if I do 50,000 INSERTs, then one UPDATE, I'll have the same problem, except moved to another command.  

The client date is not wide (maybe 3 fields--one key, two update fields); however, I think an array would not be indicated because I don't think I can Update a SQL table in VB using an array (other than one record at a time, circling right back to my problem).

Thanks
jr
' Client Data:
Dim cnnClient As ADODB.Connection
Dim sClientConnect As String
Dim strClientSQL As String
Dim rstClient As ADODB.Recordset

' SQL Data to be updated:
Dim strSql As String
Dim cmdUpdate As ADODB.Command
Dim sSqlConnect As String

' Client side setup
Set cnnClient = New ADODB.Connection
sClientConnect = "(Some successful yet proprietary ODBC connection string here)"
cnnClient.ConnectionString = sClientConnect
strClientSQL = "Select * from ClientView"
rstClient.Open strClientSQL, cnnClient, adOpenDynamic, adLockReadOnly


' SQL Data to be updated:
Set cmdUpdate = New ADODB.Command
sSqlConnect = "(Successful ODBC connection string here)"
cmdUpdate.ActiveConnection = sSqlConnect

' Scroll through client data & update the SQL data:
rstClient.MoveFirst
Do
    strSql = "Update SqlTable Set Address = '" & rstClient.Fields("Address") & "' WHERE ClientID = '" & rstClient.Fields("CustNo") & "'"
    cmdUpdate.CommandText = strSql
Loop While Not rstClient.EOF

Open in new window

0
Comment
Question by:jruhe
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 11

Expert Comment

by:kbirecki
ID: 36476601
Are you reading from the data source that is accessible via the odbc connection and writing to the sql database?
0
 
LVL 11

Accepted Solution

by:
kbirecki earned 167 total points
ID: 36476618
(Accidentally clicked submit too soon.)
Maybe you could:
1) use rstClient.GetRows() to get the source records to an array

2) write them to a CSV file (http://www.freevbcode.com/ShowCode.asp?ID=5136)

3) use Bulk Insert (http://msdn.microsoft.com/en-us/library/ms188365.aspx) to upload to the SQL server.  Additional info is 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/

and 4) once the data is uploaded, use your joined query update concept.  

This is what Bulk Insert is for.  This should be faster for large datasets.
0
 
LVL 8

Assisted Solution

by:Andrei Fomitchev
Andrei Fomitchev earned 166 total points
ID: 36480099
Your task is a quite regular one.

There is MERGE for your purpose:
it can insert if new, update if exists

1. Copy you source data as temporary table. options:
(a) Export / Import (BCP for example or Import in SSMS).
(b) SSIS - it can connect to many sources (even to xml SPREADSHEEET or FILE) directly. And you can schedule it for daily or whatever.

2. Merge tmp table with the target table - it could be scheduled, too, or be a step in SSIS.
-----------------
MERGE example:

;MERGE Production.UnitMeasure AS target
    USING (SELECT @UnitMeasureCode, @Name) AS source (UnitMeasureCode, Name)
    ON (target.UnitMeasureCode = source.UnitMeasureCode)
WHEN MATCHED THEN
        UPDATE SET Name = source.Name // <----------------- UPDATE
WHEN NOT MATCHED THEN      
          INSERT (UnitMeasureCode, Name) // <--------------------- INSERT
          VALUES (source.UnitMeasureCode, source.Name)
          OUTPUT deleted.*, $action, inserted.* INTO #MyTempTable; <--- the log of what it did
0
Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

 
LVL 4

Author Comment

by:jruhe
ID: 36490923
Thanks for these.

Kbirecki, I am indeed reading from the data source table that is accessible via the odbc connection and writing to the sql database.  However, I'm worried about doing a CSV (I don't know where the server is in relation to the client machine, so I don't think I can give it a specific location).

Fomand, What kind of code is that?  I'm trying to do this with VB6. . .

Thanks to both of you,
jr
0
 
LVL 4

Author Comment

by:jruhe
ID: 36490933
One more thing:  Can you write a VB array to a temporary table in SQL, without doing it line by line, or no?

Thanks again
0
 
LVL 75

Expert Comment

by:Anthony Perkins
ID: 36491438
>>Fomand, What kind of code is that?<<
It is called T-SQL (SQL dialect used by SQL Server).  However, if you are still using SQL Server 2005 you will fast find that MERGE is not supported.
0
 
LVL 75

Assisted Solution

by:Anthony Perkins
Anthony Perkins earned 167 total points
ID: 36491449
>>Can you write a VB array to a temporary table in SQL, without doing it line by line, or no?<<
Only if you serialize it to an Xml document or are prepared to pass it in as a delimited string to a Stored Procedure.

0
 
LVL 4

Author Closing Comment

by:jruhe
ID: 36519245
Read my last comment.  Thanks for the work-arounds, all of you!  I'll let you know what worked when I get client feedback and, if it's still too slow, work with them using one of your solutions.
0

Featured Post

Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I have a large data set and a SSIS package. How can I load this file in multi threading?
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

751 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