Solved

VB6 - Updating SQL table from a non-SQL connection

Posted on 2011-09-02
8
327 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
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 11

Expert Comment

by:kbirecki
Comment Utility
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
Comment Utility
(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
Comment Utility
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
 
LVL 4

Author Comment

by:jruhe
Comment Utility
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
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 4

Author Comment

by:jruhe
Comment Utility
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
Comment Utility
>>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
Comment Utility
>>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
Comment Utility
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

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

Join & Write a Comment

Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to shrink a transaction log file down to a reasonable size.

762 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

12 Experts available now in Live!

Get 1:1 Help Now