January 8, 2002 at 3:00 am
Morning All,
We have a workflow database which track the processing of clients applications. Once the product has been issued to a client his record is set to an archive status. I need to move these records from the main table to an archive table since there is no more processing to take place on them. Any suggestions as to how to do this simply. I assumed DTS is the way to go but there is concern about its use in here after one or two incidents
Keep it simple please major newbie in the house
January 8, 2002 at 3:15 am
DTS is great for copying and transforming data between datasources, but in your case you're dealing with just SQL Server so I'd not use it. I would use INSERT INTO tblArchive SELECT fields from other table(I'd also include a field for the datetime of the transfer). Then you need to delete the previous records. As these are two data modification queries, this should all be done in a transaction, and rolled back if the insert fails for any reason. Once the SQL is ready you need to decide how to trigger it off - the easiest way is to have a scheduled job which runs TransactSQL.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
January 8, 2002 at 3:56 am
Hi Paul,
thanks for that. How would the syntax read of the transfer part.... I'm really not very confident with t-sql yet.
We don't currently use a time stamp as once the record reaches the archive status we don't need other information. Having said that is there any reason why we should. Once it is a scheduled job we know when the transfer occurred.
January 8, 2002 at 4:57 am
In BOL have a look at INSERT INTO... Adding rows With Insert - there is an example and it's pretty straightforward. I personally wouldn't use a timestamp. I think it would be easier to use a datetime value, and more meaningful to look at.
To do this, in the Archive table add another column called DateAdded. In the TSQL, have something like this:
declare @CurrentDateTime datetime
set @CurrentDateTime = GetDate()
After that the use of literals is allowed in the Select statement, so the value @CurrentDateTime can be simply added as another field mapped to DateAdded.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
January 8, 2002 at 5:01 am
I agree with Paul's answer - right on the money! Syntax looks like this:
Insert Into tablearchive (field1, field2, etc) select field1, field2, etc from
sourcetable where readytoarchive=1
The key is that the insert and select portions have to have the same number of columns. There are other variations you can use but this one will work well.
I typically put either an identity or a uniqueidentifier column on "history" tables as the primary key along with a datetime field that I populate with a default of getdate(). Doesnt tell you when the record qualified to be archived but does tell you WHEN you archived it which is usually good enough. If you use this method you'd exclude the pkey and datetime field you added from both the insert/select, SQL populates them for you.
Transaction is the big thing - for something like this it's a have to have. You might also consider is there a chance that you'll ever 'unarchive' something by putting it back in the main table. If you do, will you remove the record from the archive table? Add a 2nd one when the unarchived record is archived again? Thats why I don't use the pkey from the source table as the pkey in the archive table - lets me have multiple rows with same source primary key.
Andy
January 8, 2002 at 5:03 am
Thanks Paul,
I'll take a look at that.
David
January 8, 2002 at 10:11 am
Write the select query first and see if it returns the info you desire. If not, post it and we'll take a look. Paul and Andy provide a great solution.
Steve Jones
January 9, 2002 at 12:42 am
Thanks folks.
There is no really need to know when the record went to archive. Once the record reaches its archive status that is generally the last business transaction. Yes, records do get moved back to the main table occasionally and are then deleted from archive. In other word the record only ever exists once and only ever in one of the tables.
The reason the records get moved is because the main table is for transaction proceesing. Once it reaches its final stage in processing nothing else happens. If I were to leave it in the main table it would just slow things down so I just ship 'em out
January 9, 2002 at 5:17 am
As for the user auditing this is really an interesting question and perhaps deserves its own topic but anyway, on projects I've worked on it depends on the architecture you're using. If each user has a different logon, then CURRENT_USER could be used. If each user has a different application, then APP_NAME. Both of these are ok within a trigger. NB these are further complicated by the use of MTS, which pools connections meaning they may or may not not work in this COM+ environment. On our VB and ASP client-server apps, we used one sql logon user for the application, so neither of these approaches would work. So, in our case we had a separate logon screen in the application which authenticated against our own list of employees and then maintained this state throughout the other screens and forms, so the userID was sent in to each stored procedure call. Each table had an additional column for userid which was the last person to edit the record and in this way the triggers could have access to the value.
Hope this helps.
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
January 9, 2002 at 5:20 am
Apologies for above - replied to wrong topic!!
Paul Ibison
Paul Ibison
Paul.Ibison@replicationanswers.com
January 9, 2002 at 10:07 am
I'd then move it to an archive table using insert ArchiveTable select ...
Steve Jones
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply