Very Computer

Let's say I have a tempoary table with four records that a user has
chosen...in this case, products on a bill of material.  I want to
update the actual production file.  In that production file, two of
the four records may already exist, and two don't...or maybe all four
exist, or maybe one exists but three don't.

I need to either insert or add all four.  How can I write one line in
T-SQL to do so?

On a straight insert where I know that the four user entries don't
exist, I do a...

INSERT productionfile FROM SELECT <fields> FROM #productionfile

And if the four already exist, I do a...

UPDATE productionfile FROM #productionfile WHERE <match the product
IDs>

But how can I write one line that would update all fields from the
temp table where product keys do exist, and inserts where they don't?

Thanks,
Kevin

  You can update, then insert:

update productionfile from #productionfile
set ...
where #productionfile.prodID = productionfile.prodID

insert into productionfile
select * from #productionfile
where not exists (
  select * from productionfile
  where #productionfile.prodID = productionfile.prodID
)

Steve Kass
Drew University

Quote:>> But how can I write one line that would update all fields from the
>> temp table where product keys do exist, and inserts where they don't

begin tran
insert into productionfile
select ...
  from #productionfile as p1
 where not exists( select * from productionfile as p2
                    where p2.prodid = p1.prodid )

update productionfile
   set ...
  from #productionfile as p1
 where p1.prodid = productionfile.prodid
commit

--
Umachandar Jayachandran
SQL Resources at http://www.umachandar.com/resources.htm
( Please reply only to newsgroup. )