update else insert - SQL Server Forums

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)

update else insert

Author

Topic

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-06-04 : 10:56:25

Hi
I have two tables tempcustomer and customer

the tempcustomer holds all the new and edit information.

the fields in it are customerid,customername,address ..so on

the customer table contains the same fields.

Whenever a new customer is added it is first added in temp customer and then inserted to customer

whenever an existing customer details are edited from the front-end,it shud be first updated in temp table and then it is updated to the live table from the temp table.

i want to design a sp which inserts a new customer from temp table to live table or if he is an existing customer
only that record shud be updated in the live table with details from temp table .

I could get upto insert but the update part iam not sure how to update only that particular record that has been changed in temptable.

dinakar
Master Smack Fu Yak Hacker

2507 Posts

Posted - 2009-06-04 : 11:00:36

[code]
IF EXISTS (SELECT * FROM customer WHERE <PK columns> = Values)
BEGIN
-- Record already exists.. so UPDATE

END
ELSE
BEGIN
-- Record does not exist.. so INSERT

END
[/code]

Dinakar Nethi
************************
Life is short. Enjoy it.
************************
http://weblogs.sqlteam.com/dinakar/

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-06-04 : 11:10:16

--Do it in two steps.
--First step is to update what already exists i.e. can be joined
update lt
set column = tt.column
from live_table lt
join temp_table tt
on lt.PK_ID = tt.PK_ID

--Second step is to insert what is not already there
insert live_table
select * from temp_table tt
where not exists (select * from live_table lt2 where lt2.PK_ID = tt.PK_ID)

No, you're never too old to Yak'n'Roll if you're too young to die.

akpaga
Constraint Violating Yak Guru

331 Posts

Posted - 2009-06-08 : 12:31:27

thanks webfred.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources