Solved

VB6 - Updating SQL table from a non-SQL connection

Posted on 2011-09-02
8
334 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Upgrading my SSIS package in VS 2012 6 62
TSQL XML Namespaces 7 24
SQL Availablity Groups List 2 8
Shrink multiple databases at once 4 31
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…
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.
Viewers will learn how the fundamental information of how to create a table.

820 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