Transferring data from one table to another

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

SQL Server 2000 Forums

SQL Server Development (2000)

Transferring data from one table to another

Author

Topic

azbatuk
Starting Member

19 Posts

Posted - 2001-11-29 : 22:39:04

Say, you have a Table1 to hold some values with 5000 records. And then at some point you have to update the values in the Table2 by adding the ones in the Table1 to the ones in the Table2. You also should reset the values in the Table1 to zero after every value is transferred.

What is the best way to do this ?

I used two ADO Recordset objets concurrently with a Do While loop. Recordset1 is reading the values in the temp table and resetting them, Recordset2 is updating the values in the main table by adding the values of Recordset1.

I am not sure this is a good way because the number of the records is high.

Can anyone suggest me a more efficient method ?

Learning is Living

berinder
Starting Member

18 Posts

Posted - 2001-11-30 : 05:00:01

I'm not quite sure i understand you, but you are first inserting values from table1 to table2. After that you are updating a field in table1 to 0 (zero) so why don't:

 Insert into table2 (field1, field2)
        Select field1, field2 from table1

and then

 update table1 set field2 = 0

Or am i not understanding you correctly?

sandesh_moghe
Constraint Violating Yak Guru

310 Posts

Posted - 2001-11-30 : 06:36:59

Hi
If you wann to update Table1 when some data is added in Table2, then i think, Trigger is the best option.

---------------------------
Sandesh - The Messanger

azbatuk
Starting Member

19 Posts

Posted - 2001-11-30 : 09:44:35

berinder,

I am not INSERTing bu UPDATing. I will write my actual code down to make the things clear.

---------------
Set RSHits_Temp = Server.CreateObject("ADODB.Recordset")
RSHits_Temp.Open "SELECT * FROM hits_temp WITH (ROWLOCK) ORDER BY username", Conn, 2, 2 ' adOpenDynamic=2, adLockPessimistic=2

Set RSUpdateHit = Server.CreateObject("ADODB.Recordset")
RSUpdateHit.Open "SELECT uniquehits, totalhits FROM hits WITH (ROWLOCK) ORDER BY username WHERE hitdate='" & Application("newDay") & "'", Conn, 2, 2 ' adOpenDynamic=2, adLockPessimistic=2

Do While not RSHits_Temp.EOF and not RSUpdateHit.EOF

RSUpdateHit("uniquehits") = RSUpdateHit("uniquehits") + RSHits_Temp("uniquehits")
RSUpdateHit("totalhits") = RSUpdateHit("totalhits") + RSHits_Temp("totalhits")
RSUpdateHit.Update

RSHits_Temp("uniquehits") = 0
RSHits_Temp("totalhits") = 0
RSHits_Temp.Update

RSUpdateHit.MoveNext
RSHits_Temp.MoveNext

Loop
---------------

Maybe this can give you a better idea about what I was trying to ask.

-------------------
Learning is Living

AjarnMark
SQL Slashing Gunting Master

3246 Posts

Posted - 2001-11-30 : 15:32:05

azbatuk,

This can all be accomplished completely in SQL. A couple of notes:
1) The Update and Delete should be inside a transaction.
2) If anything can be added/changed in Table 1 while this process is happening, you need to isolate the rows you are dealing with, perhaps with a status flag.

It would look something like this:


BEGIN TRANS
  UPDATE Table1 SET StatusFlag = 1  -- Mark all recs we are going to process in case any others get added in the middle of processing.
  UPDATE Table2 SET T2.Field = T2.Field + T1.Field
    FROM Table2 T2
    INNER JOIN Table1 T1 on T2.x = T1.x
    WHERE T1.StatusFlag = 1
  DELETE FROM Table1 WHERE StatusFlag = 1
COMMIT

Of course that's pseudo-code, but I'm sure you can fill in the appropriate details.

-------------------
It's a SQL thing...

azbatuk
Starting Member

19 Posts

Posted - 2001-11-30 : 16:50:37

AjarnMark,

Thanks for help. I'll try your suggestion.

-------------------
Learning is Living

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources