Method for Archiving Rows

I need to archive some rows from a production database. Any ideas on good
principles to follow ? I would absolutlely want to ensure that each row is
properly archived prior to deleting it from the production table. Anyone
have ideas that they used and wish to share ?
ThanksRob wrote:
> I need to archive some rows from a production database. Any ideas on go
od
> principles to follow ? I would absolutlely want to ensure that each row
is
> properly archived prior to deleting it from the production table. Anyone
> have ideas that they used and wish to share ?
Do the archive process during "off-hours."
Wrap the append (to the archive) and delete (from the production db)
statements in BEGIN TRANS and COMMIT. Use ROLLBACK if an error occurs.
See the BOL for more info.
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)|||Thanks for responding...
Something I was thinking about was linking the key from the "archive db" to
the Key to the "poduction db" and updating a flag on the prodcution. This
would ensure that the row existed in the Archive prior to deleting from
production.
"MGFoster" <me@.privacy.com> wrote in message
news:0F1Yd.6545$cN6.2489@.newsread1.news.pas.earthlink.net...
> Rob wrote:
> Do the archive process during "off-hours."
> Wrap the append (to the archive) and delete (from the production db)
> statements in BEGIN TRANS and COMMIT. Use ROLLBACK if an error occurs.
> See the BOL for more info.
> --
> MGFoster:::mgf00 <at> earthlink <decimal-point> net
> Oakland, CA (USA)|||Rob wrote:
> Thanks for responding...
> Something I was thinking about was linking the key from the "archive db" t
o
> the Key to the "poduction db" and updating a flag on the prodcution. This
> would ensure that the row existed in the Archive prior to deleting from
> production.
--BEGIN PGP SIGNED MESSAGE--
Hash: SHA1
You could update that flag after the archive process runs, but, again,
that can be done inside the transaction. You don't need to "link"
anything between the archive & production. IOW, the production table(s)
would have an "Archived" column. The archive process would only select
rows that do not have the Archived column set to true.
An example of the process:
begin tran
insert into archive_table (<column list> )
select <column list>
from table_name
where archived = 0 -- False will be zero & True will be 1
-- the default for archived should be zero
if @.@.error <> 0 goto err_
update table_name
set archived = 1
where archived = 0
if @.@.error <> 0 goto err_
commit
goto exit_
err_:
rollback
exit_:
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)
--BEGIN PGP SIGNATURE--
Version: PGP for Personal Privacy 5.0
Charset: noconv
iQA/ AwUBQjCzOYechKqOuFEgEQJu+gCePIIvgGgSjDh3
9lQY0uy3JVrybfYAoJTV
xPUUKn7k08dcCAr42/VJI7zf
=pp/t
--END PGP SIGNATURE--|||I just copy the records to the ARchive DB and then my purge solution does an
inner join to the Archive table (Or WHERE Exists()) to ensure existence
prior to deletion from prod.
works like a champ as long as proper indexes are in place, etc.
GAJ

Merging two rows

Hi All,
I have question about how to do select on below table.
This table has 3column,
Col1->Account Id[Values 1, 2, 3, 4]
Col2>Code [Values 1,2,3]
Col3>Amount[Values 100,200,300,400]
Each code maps to debit, credit, balance...
I want to do a select which can return me debit, credit and balance for
all the Account in the table.
Something like
select debit, credit, balance from Tab1.
But the diffculty is each debit, credit and balance are in a different
row. How can I merge many rows into a single select statement.
Please advise me how I can do this
Thank YouTry,
select
accountid,
sum(case when code = 1 then amount else 0 end) as debit,
sum(case when code = 2 then amount else 0 end) as credit,
sum(case when code = 3 then amount else 0 end) as balance
from
group by accountid
AMB
"dhani" wrote:

> Hi All,
> I have question about how to do select on below table.
> This table has 3column,
> Col1->Account Id[Values 1, 2, 3, 4]
> Col2>Code [Values 1,2,3]
> Col3>Amount[Values 100,200,300,400]
> Each code maps to debit, credit, balance...
> I want to do a select which can return me debit, credit and balance for
> all the Account in the table.
> Something like
> select debit, credit, balance from Tab1.
> But the diffculty is each debit, credit and balance are in a different
> row. How can I merge many rows into a single select statement.
> Please advise me how I can do this
> Thank You
>|||dhani wrote:
> Hi All,
> I have question about how to do select on below table.
> This table has 3column,
> Col1->Account Id[Values 1, 2, 3, 4]
> Col2>Code [Values 1,2,3]
> Col3>Amount[Values 100,200,300,400]
> Each code maps to debit, credit, balance...
> I want to do a select which can return me debit, credit and balance
> for all the Account in the table.
> Something like
> select debit, credit, balance from Tab1.
> But the diffculty is each debit, credit and balance are in a different
> row. How can I merge many rows into a single select statement.
> Please advise me how I can do this
> Thank You
Your requirements are not clear.
Please read and comply with www.aspfaq.com/5006
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||The post by the user provides some information that should be able to help
him out.
Allthough I encourage the use of some 'standard' posting and
recommendations, just replying that the users requirements are not clear
and he should comply with etiquette that is not even requied by the
newsgroup doesn't make sense.
What the user wants is to pivot the table in this case.
Personally I would go for a different table design.
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Bob Barrows [MVP]" <reb01501@.NOyahoo.SPAMcom> wrote in message
news:%234faQbWuFHA.3224@.TK2MSFTNGP10.phx.gbl...
> dhani wrote:
> Your requirements are not clear.
> Please read and comply with www.aspfaq.com/5006
> Bob Barrows
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>|||> Allthough I encourage the use of some 'standard' posting and
> recommendations, just replying that the users requirements are not clear
> and he should comply with etiquette that is not even requied by the
> newsgroup doesn't make sense.
Sure it does (and "make sense" is very subjective, isn't it? Just because
SELECT * and a 9-page stored procedure makes sense to you, doesn't mean we
all share that opinion). The requirements were not clear, so Bob asked for
further clarification, and showed the user a quite helpful link in providing
requirements in a generally accepted format here.
Nobody said it was required by the newsgroup (what is required by the
newsgroup anyway, I have no idea what you're talking about). But you'll see
this follow-up a lot, and I can only assume from you lack of visibility here
that it's just a fact of life in here that you haven't yet been exposed to
enough to understand.
21 letters. Wow.|||So you are judging my knowlegde?
I don't think we ever met and I would definetely not judge you, knowing that
you are a respected MVP.
Did you use a user defined function to count the letters in my
certification?
So why do you conclude I have a lack of visibility and what fact of life
have I not been exposed to?
Don't you think it would be better to just share knowledge in order to
provide solutions and results?
Dandy Weyn
[MCSE-MCSA-MCDBA-MCDST-MCT]
http://www.dandyman.net
Check my SQL Server Resource Pages at http://www.dandyman.net/sql
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:ewD9UjXuFHA.2008@.TK2MSFTNGP10.phx.gbl...
> Sure it does (and "make sense" is very subjective, isn't it? Just because
> SELECT * and a 9-page stored procedure makes sense to you, doesn't mean we
> all share that opinion). The requirements were not clear, so Bob asked
> for further clarification, and showed the user a quite helpful link in
> providing requirements in a generally accepted format here.
> Nobody said it was required by the newsgroup (what is required by the
> newsgroup anyway, I have no idea what you're talking about). But you'll
> see this follow-up a lot, and I can only assume from you lack of
> visibility here that it's just a fact of life in here that you haven't yet
> been exposed to enough to understand.
> 21 letters. Wow.
>|||> Don't you think it would be better to just share knowledge in order to
> provide solutions and results?
Yes, and part of that knowledge is learning how to post requirements that
work well, are easily re-create-able and test-able, and are requested.
You judged Bob's post and said it didn't make sense. I disagree. Period.|||Dandy Weyn [Dandyman] wrote:
> The post by the user provides some information that should be able to
> help him out.
I'm not trying to be combative or insulting, but, if the post contained
sufficient information, then why didn't you provide a solution?
Look, Alejandro made an attempt based on his guess as to what the user
needed. It may even be the correct solution. The question is: Who knows? I
could have made a similar attempt, only to have the OP come back and say:
"no that's not what I wanted at all", in which case both my time and the
OP's were wasted.

> Allthough I encourage the use of some 'standard' posting and
> recommendations, just replying that the users requirements are not
> clear and he should comply
Well, maybe "comply" was a poor choice of words: I meant "follow the
recommendations made" but I was a little strapped for time and was trying to
be concise.

> with etiquette that is not even requied by
I think if you use google, you will fiind that a large percentage of posts
to this group, as well as the comp.databases.ms-sqlserver group, receive a
similar initial request to provide DDL and sample data. This is usenet,
there is no way to "require" etiquette. That does not mean we should not
make an effort to help posters get help as efficiently as possible by
showing them the best way to provide the information needed to solve their
problems. Aaron's article does a good job of that. He put it in his
"ettiquette" section, but that does not mean this has anything to do with
"ettiquette".

> the newsgroup doesn't make sense.
Why not? What else should I do when the post contains insufficient
information to provide a solution? Waste my time and the OP's by making a
guess? Ignore the message (how does that help the OP)?
> What the user wants is to pivot the table in this case.
Maybe. In fact, I will even go so far as to say "probably". But, does he
need a dynamic pivot? Or does he have static values on which to base a
non-dynamic pivot?

> Personally I would go for a different table design.
Really? Based on what? You know very little about the OP's requirements ...
And why don't you think a vague statement about a "different table design"
is as bad as a request for clarification?
Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.