Re: faster way to write tables?

Paul Scott formulated the question :
[Snip..]
(or, if you want to be really clever (although this is probably much *too* clever)...

try
// Try to insert everything
insert into [<permanent>] select * from #<temporary>

except
// Delete all the rows which might cause a conflict
delete from [<permanent>] as perm
where exists
( select 1 from #<temporary> as temp
where ( temp.Key1=perm.Key2 and temp.Key2=perm.Key2 and... )

// Insert all the rows which won't cause a conflict
insert into [<permanent>] as perm
select * from #<temporary> as temp
where not exists
(select 1 from <permanent> as perm
where ( temp.Key1=perm.Key2 and temp.Key2=perm.Key2 and... )



[Snip...]

Well that is an example of what you shoold never do in my opinion.

I know for a fact that it will be faster to execute a

Delete * from <temp> where exists(select 1 from <Perm> where <TEmp>.id = <Perm>.ID

than to start an insert, rollback when a problem raises, delete the duplicate record(s) and start over again.

If you want a faster solution then why not something like
insert into [<permanent>]
select * from #<temporary>
Left Outer join [<permanent>] on [<permanent>].Id = #<temporary>.ID
Where [<permanent>].ID IS NULL

This will eliminate the delete step alltogether.

For the rest of the post I have no comment it is in raf lines what I have used in the past as well but I am not very content with the speed of the things and I haven't found a better solution my self for a generic solution tha will work in all DBs.

Regards
Yannis.

--
You talk a great deal about building a better world for your children, but when you are young you can no more envision a world inherited by your children than you can conceive of dying. The society you mold, you mold for yourself.
----Russell Baker-------


.