sql server programming: How to perform a Update/Insert - in SQL 2005 -- sql server programming

How to perform a Update/Insert - in SQL 2005

KarenM
12/16/2006 11:45:03 PM

sql server programming:

I have a source table and target table of the same structure.

My source table is a staging table I want to read records in my source
table and if the record does not exist in the target table do a insert
if the record is already in the target table I want to do a update if
the non-key fields have changed in the source table.

Here is a simple example:

source_sales is the source table. Target_Sales is the target table.

Source_Sales as the following columns:

Key Columns
OrderNum
ItemNum

Non-Key Columns
OrderDate
SalesOrg
DistChannel
Qty
Amount

the TargetSales as the same structure as the SourceSales table.

Please kindly share how I can do a insert/update in SQL 2005.

Thanks
Karen

Re: How to perform a Update/Insert - in SQL 2005

Shocky
12/17/2006 12:02:47 AM

1) Add a column dateLastModified to both your tables.
2) Create trigger on source table which would update the value of
dateLastModified to current date time on insert or update of the row
3) Compare the key columns of both the tables. If key columns do not
exist in target table, insert that record. If key columns present in
target table then compare the dateLAstModified values of the two
tables. If they are different then update the non key columns from
source to target table

[quoted text, click to view]

Re: How to perform a Update/Insert - in SQL 2005

Alex Kuznetsov
12/18/2006 6:56:24 AM

[quoted text, click to view]

Karen,

In addition to Matija's examples, in 2005 you can use OUTPUT clause and
sometimes get better performance:

sqlserver-tips.blogspot.com/2006/10/mimicking-deferrable-constraints-with.html

-----------------------
Alex Kuznetsov
http://sqlserver-tips.blogspot.com/
http://sqlserver-puzzles.blogspot.com/

Got something to say? Click here to post a reply to this thread.

Don't see what you're looking for? Try a search.

View other messages in the sql server programming group.