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.
| Author |
Topic |
|
FriendOfGhost
Starting Member
9 Posts |
 Posted - 2012-01-13 : 08:48:14
|
| Dear friends,I need to:copy TableA -> tableB /that will be done once of course)inserts in TableA -> tableBupdates in TableA -> tableBdeletes in tableA -> NOTHIG WILL CHANGE IN tableBI need to do that with t-sql. imagine tableB as an archive table. and records that are achived would be deleted from live table, which is tableA.ant suggestions? |
|
|
visakh16
Very Important crosS Applying yaK Herder
52326 Posts |
Posted - 2012-01-13 : 09:48:41
|
| sounds like what you need is an after trigger on tableA for all DML operations insert,update and deletewill structure of tableB be same as that of A or do you want to add other columns also like audit columns for example?------------------------------------------------------------------------------------------------------SQL Server MVPhttp://visakhm.blogspot.com/ |
 |
|
|
Kristen
Test
22859 Posts |
Posted - 2012-01-13 : 10:36:53
|
| You just trying to Audit the changes?If so don't store the current record - its in the "live" Table-A - just use a trigger to log any UPDATE or DELETE to Table-B. That way you save all the storage duplication of every current row in Table-A also being in Table-B.Our Audit Tables have two (additional) columns, compared to the "live" table: AuditAction and AuditDateTime. The Action column indicates if the action was Update or Delete, and the Date/Time is the Server Date/Time when it happened. Our actual Live rows have Create/Update time, and User (so we get that too in the Audit table)Example here:http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=170215you'll see that the Trigger is just a couple of lines of SQL, and if your audit tables always have same columns as Live table, plus the Action and Date/Time, the code will be the "same" in the triggers for all your tables |
|
|
|
Bustaz Kool
Master Smack Fu Yak Hacker
1834 Posts |
Posted - 2012-01-13 : 11:28:35
|
| You could use the MERGE statement to perform the actual insert/update processing. You would use in inside a trigger as has been previously suggested. BOL has the details on MERGE.=================================================Men shout to avoid listening to one another. -Miguel de Unamuno |
|
|
|
|
|
|
Copy/update all data from tableA to B