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.
| Author |
Topic |
|
yossibaram
Yak Posting Veteran
82 Posts |
 Posted - 2002-08-27 : 10:00:05
|
| Hi,I read a large source of records (from a text file) in order to update the db.If a record exist in the db -I need to update, if it doesn't - I need to add.What is the best way to do it?If I get a recordset for each record and do a check if to update or add, it will cost me with hips of iteration.How can I use update batch for that?ThanksYossi |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2002-08-27 : 10:12:06
|
| Insert your records into a load table - preferably in it's own database.Have an identity on this table with a clustered index on it.If you use bcp you may find it easier to use a view to avoid the identity.once there use something likecreate procedure loadtr@recs intascreate table #a (id int)while exists (select * from loadtbl)set rowcount @recsinsert #a select id from loadtbl order by idset rowcount 0update tblset ...from loadtbl, #awhere loadtbl.id = #a.idand tbl.pk = loadtbl.pkinsert tbl select ...from loadtbl, #awhere loadtbl.id = #a.idand not exists (select * from tbl where tbl.pk = loadtbl.pk)delete loadtbl from #a where loadtbl.id = #a.idendcall this SP with various values for @recs untill you get the best fit for your memory.If the file may contain inserts and updates for the same record then get IDs into #a up to the first duplicate or process the non-duplicates first then the duplicates one by one (by just setting @recs to 1).==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
Batch update problem