Data Transfer - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

ASP.NET

Data Transfer

Author

Topic

maddalimurali
Starting Member

2 Posts

Posted - 2002-11-19 : 09:44:43

Hello All,

I am have to transfer data between two tables, this sounds quite simple, but I happen to hit by performance issues. Here is my senario, I have to check for the record in the destination table and insert or update the record accordingly. Right now I am using recordset object, for this task. I cann't use update statement as I have to build the set clause, in some cases there may be more than 52 columns.

Can any one suggest me a better way of doing this? Right now it takes me more than 4 hrs to update say 20000 records, with 52 columns. This is way too long, I am running this on pIII with 256mb ram.

Is there any way I can achieve the same task from SQL queries?

Any kind of help is greatly appreciated.

Thank you,
murali.k.maddali

royv
Constraint Violating Yak Guru

455 Posts

Posted - 2002-11-19 : 10:05:54

Yes, you can do this easily in TSQL and compile it into a stored procedure. If you have to build the set clause, then you can use dynamic sql. There are several articles on this site on stored procedures and dynamic sql. Also, I can tell you right now, you *should* see a performance boost by adding more memory if you did nothing to the code.

***************************************
Death must absolutely come to enemies of the code!

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-19 : 10:16:11

How to update one table based on a set from other tables
http://www.sqlteam.com/item.asp?ItemID=3876

How to use stored procedures to update
http://www.sqlteam.com/item.asp?ItemID=2755

Is your data normalized?
http://www.databasejournal.com/features/msaccess/article.php/1442001

If you're worried about having to recreate update queries to populate a large amount of columns you can use the INFORMATION_SCHEMA views to retrieve column names and write some dynamic sql to create the stored procedures for you.

http://www.sqlteam.com/item.asp?ItemID=261
http://www.sqlteam.com/item.asp?ItemID=6948
http://www.sqlteam.com/item.asp?ItemID=4599
http://www.sqlteam.com/item.asp?ItemID=4619

If you want more precise help give details on what you are doing.

maddalimurali
Starting Member

2 Posts

Posted - 2002-11-19 : 10:52:36

Hello ValterBorges

I have gone throught the links you have send to me, but I donn't think those cases would be usefull in my case, however they where quite informative.

Here is my exact situation.

I need to transfer data between any two tables, I would be doing this from two dynamic select queries(in the sense these can be any two select queries), based on which I build to recordset objects. the source would be Readonly, forwardCursor. and the destination would be optimistic, staticCursor. I loop through source recordset and find the corresponding record in destination recordset using find or filter on the recordset and do a column by column search to see if there is a data change. and do a batch update on the destination recordset. Here is the sample code I am using, I might have miss some code during copy paste, but this gives pretty much overview of what I am doing.

-------------------------
Do Until srcRs.EOF

With dstRs
If ((UBound(sIDFieldNameArr) - LBound(sIDFieldNameArr)) > 0) Then
.Filter = sFindCriteria
Else
.Find sFindCriteria, 0, adSearchForward, adBookmarkFirst
End If
End With

If dstRs.EOF Then
If dstRs.Supports(adAddNew) Then
dstRs.AddNew
For iCounter = 0 To (iColCount - 1)
With srcRs.Fields(iCounter)
dstRs(.Name).Value = Trim(.Value)
iLoop2 = iLoop2 + 1
End With
Next iCounter
Else
' update the record here
For iCounter = 0 To (iColCount - 1)
With srcRs.Fields(iCounter)
sFieldName = Trim(.Name)
sSrcFldValue = Trim(.Value) & ""
End With

sDstFldValue = Trim(dstRs.Fields(sFieldName).Value) & ""

If dstRs.Supports(adUpdate) Then
If LenB(sDstFldValue) <= 0 And LenB(sSrcFldValue) > 0 Then
dstRs(sFieldName).Value = sSrcFldValue
iLoop = iLoop + 1
ElseIf sDstFldValue <> sSrcFldValue Then
dstRs(sFieldName).Value = sSrcFldValue
iLoop = iLoop + 1
End If
End If
Next iCounter
End If

With dstRs
If .EditMode <> adEditNone Then
iLoop3 = iLoop3 + 1
If iLoop3 Mod 10 = 0 Then
.UpdateBatch
End If
End If
End With

srcRs.MoveNext
If iLoopCtr Mod 100 = 0 Then
DoEvents
End If
Loop
dstRs.UpdateBatch
-------------------------

Thank you,
murali.k.maddali

ValterBorges
Master Smack Fu Yak Hacker

1429 Posts

Posted - 2002-11-19 : 11:46:56

I need more than ADO code to understand how to improve your process. I need table definitions, sample data, some before and after examples of the data and an pseudo-code breakdown of what you're trying to do.

quote:

Here is my senario, I have to check for the record in the destination table and insert or update the record accordingly.

I have a table for example that gets imported in a dts task from another database and I need to insert or update so I do the following:

/***** INSERT NEW RATES
INSERT INTO dbo.Rates (RateID, RateDesc, Active)
SELECT
A.RateID, A.RateDesc, 1
FROM
dbo.[Import Rates] As A LEFT OUTER JOIN
dbo.Rates As B ON A.[RateID] = B.[RateID]
WHERE
B.[RateID] Is Null

And also the following

/*****UPDATE EXISTING RATES ****/
UPDATE dbo.Rates
SET
RateDesc = A.RateDesc,
Active = 1
FROM
dbo.[Import Rates] As A INNER JOIN
dbo.Rates As B ON A.[RateID] = B.[RateID]

quote:

I cann't use update statement as I have to build the set clause, in some cases there may be more than 52 columns.

Use dynamic sql to query the INFORMATION_SCHEMA VIEWS, drop and recreate your update and insert statements with the proper columns.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources