Is a Batch Update possible? 1

[Craigmacs]

Hello

I have a Production table that needs to be updated every day.(replication or log shipping is not an option, must use dts or an sp)

Currently I have a sp that returns the data in a cursor and loops throuh the cursor to insert or update the data. This is slow and a pain to maintain.

I was thinking of a table variable to hold the new records, then insert them. Another table variable to hold the updates but I can't get it to work with out a cursor to go one record at a time.

Any idea's on a better way? Does a batch update exist?

Thanks for the help!

 

[SQLSister]

Yes, you should never update using a cursor!

Post the code to your cursor for help writing a set-based statment. Withtout knowing what you are doing to the data in the cursor, it is impossible to tell you exactly the best method.

Several things to look into in Books Onlineto give you a place to start looking yourself:
Look at using the update statement referencing another table
Look at the case statement.

Questions about posting. See faq183-874

 

[Craigmacs]

SQL Sister

Thank you for the post, I will check the suggested topics. I don't have access to my code right now so I attempted to reproduce a simple version.

Declare @Account int, @col1
DECLARE CurDaily CURSOR FOR
SELECT Account,Col1
FROM tableWithNewData

OPEN CurDaily
FETCH NEXT FROM CurDaily into @acount,@col1
WHILE @@FETCH_STATUS = 0
BEGIN

Select Top 1 * from UpdateTable where account=@account
IF @@Rowcount > 0
Begin
Update UpdateTable Set Col1=@col1
End
ELSE
Begin
Insert into UpdateTable values @account,@col1
END
FETCH NEXT FROM CurDaily
END

CLOSE CurDaily
DEALLOCATE CurDaily

 

[Craigmacs]

SQL Sister - Use this

Thank you for the post, I will check the suggested topics. I don't have access to my code right now so I attempted to reproduce a simple version.

Declare @Account int, @col1 int
DECLARE CurDaily CURSOR FOR
SELECT Account,Col1
FROM tableWithNewData

OPEN CurDaily
FETCH NEXT FROM CurDaily into @acount,@col1
WHILE @@FETCH_STATUS = 0
BEGIN

Select Top 1 * from UpdateTable where account=@account
IF @@Rowcount > 0
Begin
Update UpdateTable Set Col1=@col1 where account=@account
End
ELSE
Begin
Insert into UpdateTable values @account,@col1
END
FETCH NEXT FROM CurDaily
END

CLOSE CurDaily
DEALLOCATE CurDaily

 

[JohnDTampaBay]

How does this work for you? Good luck!

-- Update accounts that exist
UPDATE u
SET Col1 = twnd.col1
FROM UpdateTable ut
  INNER JOIN tableWithNewData twnd
    ON ut.Account = twnd.Account

-- Insert accounts that don't exist
INSERT INTO UpdateTable (Account, Col1)
SELECT Account, Col1
FROM tableWithNewData twnd
WHERE NOT EXISTS(SELECT * FROM UpdateTable ut
                 WHERE ut.Account = twnd.Account)

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)