Archive data before deletion

Hi all,
how can I copy all dependant child records into duplicate tables before
deleting them.
The situation is that I have a master table "customer" with 20 other
tables that depend on this master table.
Foreign keys are all set up correctly and cascading delete is enabled.
Now when a customer wants to cancel his subscription, I don't want to
delete all referenced data immediatly without saving, because I need
them for possible future references, like billing addr. etc.
Currently I'm setting a "Deleted" flag so that in any query this
customer doesn't show up.
What would be the best approach to archive all dependent data before
deleting the parent and the child records.
I'm thinking of duplicate tables and/or a duplicate database.
I've also played around with triggers but can't get the automatic
insert of the child records working.
Do I really have to do something like this for every child table?
Insert into dupAddr (select * from addr where customerid = 1)
Insert into dupTrx (select * from trx where customerid = 1)
Insert into dupCustomer (select * from customer where customerid = 1)
etc.
I'm using SQL2000/W2K3.
Any help would be appreciated.
thx in advance,
ChrisThe two-database approach is a good way of doing this. Instead of using the
Deleted flag, you can now issue actual delete statements to remove old
records, but not before you design an ON DELETE trigger to propagate the
deleted rows to the archive database.
So, yes - you do need all those queries... :) This is what being a database
designer is all about.
Lookup CREATE TRIGGER in Books Online. Designing these triggers is easy -
simply use the 'deleted' table.
Example:
insert archive_db.dbo.table1
(...columns...)
select ...columns...
from deleted
Don't forget to include appropriate error-handling, so no delete goes
unnoticed.
ML|||I have an example here: http://vyaskn.tripod.com/sql_archive_data.htm
--
HTH,
Vyas, MVP (SQL Server)
SQL Server Articles and Code Samples @. http://vyaskn.tripod.com/
<devccon@.gmx.de> wrote in message
news:1122542261.075469.237440@.f14g2000cwb.googlegroups.com...
Hi all,
how can I copy all dependant child records into duplicate tables before
deleting them.
The situation is that I have a master table "customer" with 20 other
tables that depend on this master table.
Foreign keys are all set up correctly and cascading delete is enabled.
Now when a customer wants to cancel his subscription, I don't want to
delete all referenced data immediatly without saving, because I need
them for possible future references, like billing addr. etc.
Currently I'm setting a "Deleted" flag so that in any query this
customer doesn't show up.
What would be the best approach to archive all dependent data before
deleting the parent and the child records.
I'm thinking of duplicate tables and/or a duplicate database.
I've also played around with triggers but can't get the automatic
insert of the child records working.
Do I really have to do something like this for every child table?
Insert into dupAddr (select * from addr where customerid = 1)
Insert into dupTrx (select * from trx where customerid = 1)
Insert into dupCustomer (select * from customer where customerid = 1)
etc.
I'm using SQL2000/W2K3.
Any help would be appreciated.
thx in advance,
Chris|||Thanks to you all for the directions. I'm still struggling with the
trigger but it shouldn't be that of a problem.
I really hoped there would be some other way, but, hey anything that
does the job is good...
Thx again,
Chris
devccon@.gmx.de wrote:
> Hi all,
> how can I copy all dependant child records into duplicate tables before
> deleting them.
> The situation is that I have a master table "customer" with 20 other
> tables that depend on this master table.
> Foreign keys are all set up correctly and cascading delete is enabled.
> Now when a customer wants to cancel his subscription, I don't want to
> delete all referenced data immediatly without saving, because I need
> them for possible future references, like billing addr. etc.
> Currently I'm setting a "Deleted" flag so that in any query this
> customer doesn't show up.
> What would be the best approach to archive all dependent data before
> deleting the parent and the child records.
> I'm thinking of duplicate tables and/or a duplicate database.
> I've also played around with triggers but can't get the automatic
> insert of the child records working.
> Do I really have to do something like this for every child table?
> Insert into dupAddr (select * from addr where customerid = 1)
> Insert into dupTrx (select * from trx where customerid = 1)
> Insert into dupCustomer (select * from customer where customerid = 1)
> etc.
> I'm using SQL2000/W2K3.
> Any help would be appreciated.
> thx in advance,
> Chris

Applying MS05-003 broke something...

Hi folks. I have a customer who applied MS05-003 (KB871259) to their server
running MS SQLServer 2000 SP3. On doing this, certain data uploads began to
fail. Once this patch was removed, things began working again. Is there a
known issue with this patch? Has anyone else run across a similar issue? If
so, can someone suggest a work-around or other solution?
Thank you.
Daniel.Please confirm the patch you have installed/un-installed.
MS05-003 is not KB871259
http://www.microsoft.com/technet/se...n/MS05-003.mspx
is 871250.
This patch affect the Indexing Service and replaces the following file on
Win2k
Ciodm.dll
5.0.2195.6981
05-Nov-2004
04:41
68,880
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||Kevin,
My bad. I typoed. The patch applied is MS05-003 / 871250.
Suggestions or known issues?
Daniel.
"Kevin McDonnell [MSFT]" wrote:

> Please confirm the patch you have installed/un-installed.
> MS05-003 is not KB871259
> http://www.microsoft.com/technet/se...n/MS05-003.mspx
> is 871250.
> This patch affect the Indexing Service and replaces the following file on
> Win2k
> Ciodm.dll
> 5.0.2195.6981
> 05-Nov-2004
> 04:41
> 68,880
>
> Thanks,
> Kevin McDonnell
> Microsoft Corporation
> This posting is provided AS IS with no warranties, and confers no rights.
>
>|||I'm not aware of this particular patch affecting SQL Server, since we're
patching the Indexing Service.
The Indexing Service is a base service for MicrosoftWindows 2000 or later
that extracts content from files and constructs an indexed catalog to
facilitate efficient and rapid searching. It is used for local searching
and can also be utilized on an IIS Web server to search remotely (with
custom web application code)
Please confirm that removing the patch allows your SQL Server to function
correctly. Open up a case with us if you're able to reproduce the problem.
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.|||"Kevin McDonnell [MSFT]" wrote:[vbcol=seagreen]
> Please confirm that removing the patch allows your SQL Server to function
> correctly. Open up a case with us if you're able to reproduce the problem.[/vbcol
]
Kevin, Confirmed. As for "opening a case", I'd appreciate just a little
direction on exactly how to do that. I have looked with little success on th
e
web site and nothing just jumps out and shouts "log a case here". Clues are
sufficient (ususally).
d.|||Call Support Customer Care:
1-800-936-3500 (U.S. and Canada Only)
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Appending to a Text field

Hi Guys
I am trying to append to a text (text data type) field in my database.
Simply put, the Customer table has a field called Notes. I want to append
some extra data to this field via TSQL. So for a Customer who has an ID of
1234, how do I append some extra text to his Notes field? The help in Books
on Line has me completely baffled.
Thank youWhich helo are you referring to? Have you looked at WRITETEXT/UPDATETEXT?
ML
http://milambda.blogspot.com/

Append information to data already in a column

Just recently I was thrown into the DBA roll... needless to say I'm not
a DBA. The customer has requested a query to take a column full of
numerical data (a 6 digit number) and run a query to append a .00
behind that number for every record in that column. Is this possible?
I've checked the online books and have done countless searches for a
hint of a query to no avail.
Any and all help would be greatly appreciated.
Thank you,
JIf you just want to represent the (integer?) number as decimal with '.00'
SELECT CAST([YourNumber] AS decimal(8,2)) AS "number"
FROM [YourTable]
If you actually want to change how the number is stored in the database:
ALTER TABLE [YourTable] ALTER COLUMN [YourNumber] decimal(8,2)
"JMiller" wrote:

> Just recently I was thrown into the DBA roll... needless to say I'm not
> a DBA. The customer has requested a query to take a column full of
> numerical data (a 6 digit number) and run a query to append a .00
> behind that number for every record in that column. Is this possible?
> I've checked the online books and have done countless searches for a
> hint of a query to no avail.
> Any and all help would be greatly appreciated.
> Thank you,
> J
>|||You have not told us the current data type of the column, and that
makes all the difference.
If it is an integer (bigint, int, smallint, tinyint) then the data
type would need to change to DECIMAL (or NUMERIC) with two decimal
places.
If it is already a decimal (or numeric) then the type has to change to
add two decimal places, being careful to keep sufficient length.
If the column is character, it needs to be lengthened by three
characters, and then updated to append the '.00' string after the data
that is already there.
If the column is varchar, it MIGHT need to be lengthened, and then it
would be updated as for char.
So, what is the column's data type?
Roy
On 15 Feb 2006 15:16:36 -0800, "JMiller" <Gloranard@.gmail.com> wrote:

>Just recently I was thrown into the DBA roll... needless to say I'm not
>a DBA. The customer has requested a query to take a column full of
>numerical data (a 6 digit number) and run a query to append a .00
>behind that number for every record in that column. Is this possible?
>I've checked the online books and have done countless searches for a
>hint of a query to no avail.
>Any and all help would be greatly appreciated.
>Thank you,
>J|||I apologize for forgetting to add that important piece of information.
The table is currently configured varchar (25).|||>I apologize for forgetting to add that important piece of information.
> The table is currently configured varchar (25).
update mytable set mycolumn = rtrim(mycolumn) + '.00'
where mycolumn is not null
Things to consider.
* The maximum size of any value in mycolumn is less than 23 (since you are
adding three additional characters). How to do this: select
max(len(mycolumn)) from mytable
* Verify that this is actually appropriate for the data found in the column
for all rows. In other words, are all values "properly" numeric.
* Perhaps the better approach is to change the datatype of the column.
Alternatively, create a computed column (or view) that does this for you.
Why? Well, the values that are currently in the table got there somehow.
It is likely that this functionality will continue to use the same format
when inserting or updating rows.
* You might need additional criteria in your where clause. You might not
want to change an empty string to '.00' (which is what the above query will
do).
* For testing purposes, you can wrap the above statement in a transaction.
If you rollback the transaction, you can issue the statement multiple times
and view the results. E.g.,
begin tran
update mytable set ...
select mycolumn from mytable
rollback tran|||Thanks you both for the help, it worked like a champ. Thank you, thank
you, thank you!