Writing a move and update storedprocedure. Sql2000 | SQL Server Performance Forums

SQL Server Performance Forum – Threads Archive

Writing a move and update storedprocedure. Sql2000

Hi,
I need to write a stored Procedure that will be able to write to a table, but before
it writes to the table it needs to check if the record exists and if so it must
first move it to a History table and then update the record in the first table where it is writing to. Like Insert into Table
if exists (select * from table where a.id1 = 123)
then if it does not exist it must write it into "table"
but if it does exist then it must move it to "tablehistory" and then just update the
record in the "table" table. Any suggestions?
You should probably have a test with a couple subqueries for this if you want to avoid recompiles and have the fastest execution plan. First procedure: IF (SELECT column_list FROM table WHERE id1 = 123) IS NOT NULL
BEGIN
EXEX TableUpdate
END
ELSE
BEGIN
EXEC TableInsert
END
TableUpdate would insert to tablehistory and update changed fields.
TableInsert would just insert new row(s). MeanOldDBA
[email protected] When life gives you a lemon, fire the DBA.
Thanks for the help.
]]>