Solved

VB6 - Updating SQL table from a non-SQL connection

Posted on 2011-09-02
8
333 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
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
Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

 
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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
This query failed in sql 2014 5 31
always on switch back after failover 2 35
what are the unique tables in SQL master database 5 62
Create snapshot on MSSQL 2012 3 19
Over the years I have built up my own little library of code snippets that I refer to when programming or writing a script.  Many of these have come from the web or adaptations from snippets I find on the Web.  Periodically I add to them when I come…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
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
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

809 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