Copy Row

[fenneraj]

Hi All,

I have a problem whereby i need to copy a row of data from one table to another, but i need to do this across a large amount of tables...

For example, I have a client details table (CL_DETAILS) and a historic client Details table (CL_DETAILS_HISTORY). What i need to do is establish an SQL command that can be pushed from a web page to copy a single row from the CL_DETAILS table into the CL_DETAILS_HISTORY without deleting the original row (this is so it can then be updated).

I am praying there is some sort of SQL admin command that will sort this for me, I know i can do it with another insert statement, but that will be a very large re-build of the web pages that query and insert data into these tables.

I thank you in advance for any help you can provide,

Regards

Andy Fenner

 

[JamesLean]

I think a trigger is what you need here. Something like:

CREATE TRIGGER tr_cl_details_ins
  ON cl_details
  FOR UPDATE
AS

INSERT cl_details_history
SELECT * FROM deleted
GO

This will make a copy of the old row data every time it is updated.

--James

 

[donutman]

If you are performing the update with a SP (Stored Procedure), then you could just add the logic there and avoid the use of a trigger. Triggers should be avoided if possible. If you add a trigger for the purpose of this application, you'll be changing the behavior of the table for all other applications that use that table.
There are other issues to consider when using triggers, so you should have your eyes wide open, before using them.
-Karl

[red] Cursors, triggers, user-defined functions and dynamic SQL are an axis of evil![/red]
[green]Life's uncertain...eat dessert first...www.deerfieldbakery.com[/green]

 

[fenneraj]

James,
That works great as a trigger, but i need to be able to create the historic record on certain updates only.

Is there anyway i can add a condition to the trigger that will only cause it to copy to the historic table if the condition is true. Also i need to be able to timestamp the new record in the history table.

Other than that, the soultion works great.

Cheers

Andy

 

[JamesLean]

1) Yes, you can put logic in your trigger. If you want to test if a certain column was updated you can use:

IF UPDATE(my_column)
BEGIN
  INSERT...
END

2) I would add a datetime column to the history table and give it a default of GETDATE(). If you do this remember you will have to explicitly specify the column list in your INSERT statement.

INSERT cl_details_history (c1, c2, c3)
SELECT c1, c2, c3
FROM deleted

--James