History Cross-Tab via Cursors

I have a table with a PK of id and timestamp, plus other variables: an
update of a row adds another row with the same id but a later timestamp. I
need to create a report that lists the variables that have changed. The
following proof of concept code works fine, but I was wondering if the same
thing can be done more simply--perhaps without cursors.
Thanks for taking a look. All comments appreciated.
--FINAL RESULT TABLE FOR HISTORY REPORTING
drop table rpt_tbl
go
create table rpt_tbl
(fld_name varchar(20),
old_rpt_id int,
old_rpt_timestamp datetime,
old_fld varchar(20),
new_rpt_id int,
new_rpt_timestamp datetime,
new_fld varchar(20))
--TEST DATA
create table PROD
(prod_id int,
prod_timestamp datetime,
prod_name varchar(20),
prod_categ varchar(20))
insert prod
(prod_id, prod_timestamp, prod_name, prod_categ)
values
(1, '2004/07/05', 'Acme', 'Steel')
insert prod
(prod_id, prod_timestamp, prod_name, prod_categ)
values
(1, '2004/08/08', 'Best', 'Steel')
insert prod
(prod_id, prod_timestamp, prod_name, prod_categ)
values
(2, '2004/07/10', 'Pink', 'Color')
insert prod
(prod_id, prod_timestamp, prod_name, prod_categ)
values
(2, '2005/01/05', 'Red', 'Color')
insert prod
(prod_id, prod_timestamp, prod_name, prod_categ)
values
(2, '2005/02/17', 'Fuchsia', 'Shade')
insert prod
(prod_id,prod_timestamp, prod_name, prod_categ)
values
(3, '2005/02/17', 'Ivory', 'Shade')
insert prod
(prod_id,prod_timestamp, prod_name, prod_categ)
values
(4, '2005/4/22', 'Yellow', 'Color')
--VIEW
--GET ROWS IN PROD TABLE WITH UDPATE HISTORY
--that is, multiple rows for same prod_id, with different timestamps
create view DUPES as
select * from prod
where prod_id in
(select prod_id from prod
group by prod_id
having count(*) > 1)
--CREATE 1-ROW TABLE FOR COMPARE
create function create_table
(@.prod_id int,
@.prod_timestamp datetime)
returns table
as return
(select
prod_id, prod_timestamp,
prod_name, prod_categ
from dupes where
(prod_id = @.prod_id and prod_timestamp = @.prod_timestamp))
--COMPARE 2 ROWS
create proc compare_2_rows
(@.a_prod_id int,
@.a_prod_timestamp datetime,
@.b_prod_id int,
@.b_prod_timestamp datetime)
as
declare
@.a_prod_name varchar(20),
@.a_prod_categ varchar(20),
@.b_prod_name varchar(20),
@.b_prod_categ varchar(20)
declare xc2 cursor for
select * from
create_table (@.a_prod_id, @.a_prod_timestamp) t1
join
create_table (@.b_prod_id, @.b_prod_timestamp) t2
on t1.prod_id = t2.prod_id
open xc2
fetch next from xc2 into
@.a_prod_id, @.a_prod_timestamp,
@.a_prod_name, @.a_prod_categ,
@.b_prod_id, @.b_prod_timestamp,
@.b_prod_name, @.b_prod_categ
while @.@.fetch_status = 0
begin
if @.a_prod_name <> @.b_prod_name
insert rpt_tbl
(fld_name,
old_rpt_id, old_rpt_timestamp, old_fld,
new_rpt_id, new_rpt_timestamp, new_fld)
values
('name',
@.a_prod_id, @.a_prod_timestamp, @.a_prod_name,
@.b_prod_id, @.b_prod_timestamp, @.b_prod_name)
if @.a_prod_categ <> @.b_prod_categ
insert rpt_tbl
(fld_name,
old_rpt_id, old_rpt_timestamp, old_fld,
new_rpt_id, new_rpt_timestamp, new_fld)
values
('categ',
@.a_prod_id, @.a_prod_timestamp, @.a_prod_categ,
@.b_prod_id, @.b_prod_timestamp, @.b_prod_categ)
fetch xc2 into
@.a_prod_id, @.a_prod_timestamp,
@.a_prod_name, @.a_prod_categ,
@.b_prod_id, @.b_prod_timestamp,
@.b_prod_name, @.b_prod_categ
end
close xc2
deallocate xc2
create proc history_proc as
--OVERALL PROC
--1. empty report table
--2. use cursor to find paired PK's
--3. exec proc compare_2_rows with PK params for paired data
-- a. use cursor to join function tables for paired data
-- b. compare 2 prod_names and insert paired data into
-- rpt_tbl
-- c. compare 2 prod_categs and insert paired data into
-- rpt_tbl
truncate table rpt_tbl --empty report table
declare --the keys for the rows to be compared
--@.a refers to the older row
--@.b refers to the newer (more recent) row
@.a_prod_id int,
@.a_prod_timestamp datetime,
@.b_prod_id int,
@.b_prod_timestamp datetime
declare xc cursor for select prod_id, prod_timestamp
from dupes
open xc
--1st record (older row)
fetch xc into @.a_prod_id, @.a_prod_timestamp
--2nd record (newer row): has to be at least a pair for same id
fetch next from xc into @.b_prod_id, @.b_prod_timestamp
while @.@.fetch_status = 0
begin
--if a break in keys, don't compare them,
--and move newer key to older key variables
if @.b_prod_id <> @.a_prod_id
begin
set @.a_prod_id = @.b_prod_id
set @.a_prod_timestamp = @.b_prod_timestamp
goto skip
end
exec compare_2_rows
@.a_prod_id, @.a_prod_timestamp,
@.b_prod_id, @.b_prod_timestamp
--after compare, move newer key to older key variables
set @.a_prod_id = @.b_prod_id
set @.a_prod_timestamp = @.b_prod_timestamp
skip:
--get key for new row
fetch next from xc into @.b_prod_id, @.b_prod_timestamp
end
close xc
deallocate xc
--THIS RUNS THE OVERALL PROC
exec history_proc
--THIS SHOWS WHAT'S IN THE FINAL RESULTS TABLE
select * from rpt_tblBased on your example data, the primary key should be prod_id ,
prod_categ and prod_timestamp.
Try this:
select ProdNew.prod_id
, ProdNew.prod_categ
, ProdNew.prod_timestamp
, ProdNew.prod_name
, ProdOld.prod_timestamp
, ProdOld.prod_name
from (select Prod6.prod_id, Prod6.prod_categ ,
max(Prod6.prod_timestamp)
from Prod as Prod6
group by Prod6.prod_id, Prod6.prod_categ
) as ProdLastest ( prod_id, prod_categ , prod_timestamp)
join Prod as ProdNew
on ProdNew.prod_id = ProdLastest.prod_id
and ProdNew.prod_categ = ProdLastest.prod_categ
and ProdNew.prod_timestamp = ProdLastest.prod_timestamp
left outer join
(
select Prod1.prod_id
, Prod1.prod_categ
, Prod1.prod_timestamp
, Prod1.prod_name
from Prod as Prod1
join (select Prod.prod_id, Prod.prod_categ , max(Prod.prod_timestamp)
from Prod
join (select prod_id, prod_categ , max(prod_timestamp)
from Prod
group by Prod.prod_id, Prod.prod_categ
) as Prod3 ( prod_id, prod_categ , prod_timestamp)
on Prod.prod_id = Prod3.prod_id
and Prod.prod_categ = Prod3.prod_categ
and Prod.prod_timestamp < Prod3.prod_timestamp
group by Prod.prod_id, Prod.prod_categ
) as ProdOlder ( prod_id, prod_categ , prod_timestamp)
on Prod1.prod_id = ProdOlder.prod_id
and Prod1.prod_categ = ProdOlder.prod_categ
and Prod1.prod_timestamp = ProdOlder.prod_timestamp
) as ProdOld (prod_id, prod_categ, prod_timestamp, prod_name)
on ProdOld.prod_id = ProdNew.prod_id
and ProdOld.prod_categ = ProdNew.prod_categ
order by ProdNew.prod_id
, ProdNew.prod_categ
*** Sent via Developersdex http://www.examnotes.net ***|||Check out the doc for the RAC utility.
Focus on the 'What' instead of the 'How'.
www.rac4sql.net|||Thanks--I'll have to take a look at your code in the morning. I'm too blear
y.
The pk is prod_id/prod_timestamp. In the real world, the timestamp would
have year-month-day-time.
There's no limit to the possible number of rows per id. I need to compare
every column (there will be about 10 or 20, in the real world) in every row
for the same prod_id, so that the final table has a row for every old/new
column pair which are different.
"Carl Federl" wrote:

> Based on your example data, the primary key should be prod_id ,
> prod_categ and prod_timestamp.
> Try this:
> select ProdNew.prod_id
> , ProdNew.prod_categ
> , ProdNew.prod_timestamp
> , ProdNew.prod_name
> , ProdOld.prod_timestamp
> , ProdOld.prod_name
> from (select Prod6.prod_id, Prod6.prod_categ ,
> max(Prod6.prod_timestamp)
> from Prod as Prod6
> group by Prod6.prod_id, Prod6.prod_categ
> ) as ProdLastest ( prod_id, prod_categ , prod_timestamp)
> join Prod as ProdNew
> on ProdNew.prod_id = ProdLastest.prod_id
> and ProdNew.prod_categ = ProdLastest.prod_categ
> and ProdNew.prod_timestamp = ProdLastest.prod_timestamp
> left outer join
> (
> select Prod1.prod_id
> , Prod1.prod_categ
> , Prod1.prod_timestamp
> , Prod1.prod_name
> from Prod as Prod1
> join (select Prod.prod_id, Prod.prod_categ , max(Prod.prod_timestamp)
> from Prod
> join (select prod_id, prod_categ , max(prod_timestamp)
> from Prod
> group by Prod.prod_id, Prod.prod_categ
> ) as Prod3 ( prod_id, prod_categ , prod_timestamp)
> on Prod.prod_id = Prod3.prod_id
> and Prod.prod_categ = Prod3.prod_categ
> and Prod.prod_timestamp < Prod3.prod_timestamp
> group by Prod.prod_id, Prod.prod_categ
> ) as ProdOlder ( prod_id, prod_categ , prod_timestamp)
> on Prod1.prod_id = ProdOlder.prod_id
> and Prod1.prod_categ = ProdOlder.prod_categ
> and Prod1.prod_timestamp = ProdOlder.prod_timestamp
> ) as ProdOld (prod_id, prod_categ, prod_timestamp, prod_name)
> on ProdOld.prod_id = ProdNew.prod_id
> and ProdOld.prod_categ = ProdNew.prod_categ
> order by ProdNew.prod_id
> , ProdNew.prod_categ
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>

Historical Data Problem

We have a database that adds over 100,000 records per day. This goes back to 2002 and I only need historical data for 6 months. Presently we can only can delete 1000 row at a time. Is there a faster way of deleting. We seem to continuely run out of disk space. Urgent!!!!!!!!!!!If you just need a small set of data from a much larger set, you are better off insert those data you need into a new table, drop the old table, and rename the new table to the old table's name.|||After deciding to pull data out of the large table going back 6 months only. I created a statement that pulls each field out and will insert into a temp table. I get this error message.
'Server: Msg 544, Level 16, State 1, Line 1
Cannot insert explicit value for identity column in table 'INCOMING_TEMP' when IDENTITY_INSERT is set to OFF.'
I'm not sure what this means. Is it that I cannot insert into a ID field?|||Check 'SET IDENTITY_INSERT' in the BOL.|||? Check the bol???|||Books... On... Line... B.O.L.|||Due to the forum being down. I did some reserch on the error message and found a solution. I went into the management properties of the new temp_table and made it able to except inserts. The update is running as we speak. It's been running about 20 minutes and that's about right. The date range hold about 1,500,000 record per month. And that's why I'm clearing it out and holding only 6 months of backup historical data. Day 2 of new project.|||What your tranny logs and tempdb....

Are you doing this in batches with transactions?

A permanent table I think would have been better

Historical Data Problem

We have a database that adds over 100,000 records per
day. This goes back to 2002 and I only need historical
data for 6 months. Presently we can only can delete 1000
row at a time. Is there a faster way of deleting. We
seem to continuely run out of disk space.
Urgent!!!!!!!!!!!
Can you tell me why you can only delete 1000 rows at a time?
Jeff Duncan
MCDBA, MCSE+I
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!
|||I also don't understand what you mean by "we can only delete 1000 row at a
time"... what happens when you try to delete 1001?
I recommend halting the inserts briefly, copy the last 6 months of data into
a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the old
table, rename the new table.
Then, set up a job that runs daily (or maybe more often) that deletes any
data more than 6 months old (184 days is probably the safest to account for
31-day months). This way your window will always be roughly six months, and
you won't be tripping over yourself trying to trim it down. Automate it as
much as possible, it's less taxing on both the system and your hair.
However, if you are running out of disk space now, what happens when your
volume increases and you are adding 200k, 500k, 1m rows per day?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!
|||Because we at present, time out error occur when
attempting to delete more than 1000.
>--Original Message--
>Can you tell me why you can only delete 1000 rows at a
time?
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"Garry Dawkins" <anonymous@.discussions.microsoft.com>
wrote in message[vbcol=seagreen]
>news:798401c4311f$5f08b590$a301280a@.phx.gbl...
1000
>
>.
>
|||I agree with Aaron. Also what criteria is in your delete command. Is the
value your deleting against indexed.
Jeff Duncan
MCDBA, MCSE+I
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%238mJPFSMEHA.628@.TK2MSFTNGP11.phx.gbl...
> I also don't understand what you mean by "we can only delete 1000 row at a
> time"... what happens when you try to delete 1001?
> I recommend halting the inserts briefly, copy the last 6 months of data
into
> a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the
old
> table, rename the new table.
> Then, set up a job that runs daily (or maybe more often) that deletes any
> data more than 6 months old (184 days is probably the safest to account
for
> 31-day months). This way your window will always be roughly six months,
and
> you won't be tripping over yourself trying to trim it down. Automate it
as
> much as possible, it's less taxing on both the system and your hair.
> However, if you are running out of disk space now, what happens when your
> volume increases and you are adding 200k, 500k, 1m rows per day?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
> news:798401c4311f$5f08b590$a301280a@.phx.gbl...
>

Historical Data Problem

We have a database that adds over 100,000 records per
day. This goes back to 2002 and I only need historical
data for 6 months. Presently we can only can delete 1000
row at a time. Is there a faster way of deleting. We
seem to continuely run out of disk space.
Urgent!!!!!!!!!!!Can you tell me why you can only delete 1000 rows at a time?
--
Jeff Duncan
MCDBA, MCSE+I
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!|||I also don't understand what you mean by "we can only delete 1000 row at a
time"... what happens when you try to delete 1001?
I recommend halting the inserts briefly, copy the last 6 months of data into
a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the old
table, rename the new table.
Then, set up a job that runs daily (or maybe more often) that deletes any
data more than 6 months old (184 days is probably the safest to account for
31-day months). This way your window will always be roughly six months, and
you won't be tripping over yourself trying to trim it down. Automate it as
much as possible, it's less taxing on both the system and your hair.
However, if you are running out of disk space now, what happens when your
volume increases and you are adding 200k, 500k, 1m rows per day?
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!|||Because we at present, time out error occur when
attempting to delete more than 1000.
>--Original Message--
>Can you tell me why you can only delete 1000 rows at a
time?
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"Garry Dawkins" <anonymous@.discussions.microsoft.com>
wrote in message
>news:798401c4311f$5f08b590$a301280a@.phx.gbl...
>> We have a database that adds over 100,000 records per
>> day. This goes back to 2002 and I only need historical
>> data for 6 months. Presently we can only can delete
1000
>> row at a time. Is there a faster way of deleting. We
>> seem to continuely run out of disk space.
>> Urgent!!!!!!!!!!!
>
>.
>|||I agree with Aaron. Also what criteria is in your delete command. Is the
value your deleting against indexed.
--
Jeff Duncan
MCDBA, MCSE+I
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%238mJPFSMEHA.628@.TK2MSFTNGP11.phx.gbl...
> I also don't understand what you mean by "we can only delete 1000 row at a
> time"... what happens when you try to delete 1001?
> I recommend halting the inserts briefly, copy the last 6 months of data
into
> a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the
old
> table, rename the new table.
> Then, set up a job that runs daily (or maybe more often) that deletes any
> data more than 6 months old (184 days is probably the safest to account
for
> 31-day months). This way your window will always be roughly six months,
and
> you won't be tripping over yourself trying to trim it down. Automate it
as
> much as possible, it's less taxing on both the system and your hair.
> However, if you are running out of disk space now, what happens when your
> volume increases and you are adding 200k, 500k, 1m rows per day?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
> news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> > We have a database that adds over 100,000 records per
> > day. This goes back to 2002 and I only need historical
> > data for 6 months. Presently we can only can delete 1000
> > row at a time. Is there a faster way of deleting. We
> > seem to continuely run out of disk space.
> > Urgent!!!!!!!!!!!
>

Historical Data Problem

We have a database that adds over 100,000 records per
day. This goes back to 2002 and I only need historical
data for 6 months. Presently we can only can delete 1000
row at a time. Is there a faster way of deleting. We
seem to continuely run out of disk space.
Urgent!!!!!!!!!!!Can you tell me why you can only delete 1000 rows at a time?
Jeff Duncan
MCDBA, MCSE+I
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!|||I also don't understand what you mean by "we can only delete 1000 row at a
time"... what happens when you try to delete 1001?
I recommend halting the inserts briefly, copy the last 6 months of data into
a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the old
table, rename the new table.
Then, set up a job that runs daily (or maybe more often) that deletes any
data more than 6 months old (184 days is probably the safest to account for
31-day months). This way your window will always be roughly six months, and
you won't be tripping over yourself trying to trim it down. Automate it as
much as possible, it's less taxing on both the system and your hair.
However, if you are running out of disk space now, what happens when your
volume increases and you are adding 200k, 500k, 1m rows per day?
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
news:798401c4311f$5f08b590$a301280a@.phx.gbl...
> We have a database that adds over 100,000 records per
> day. This goes back to 2002 and I only need historical
> data for 6 months. Presently we can only can delete 1000
> row at a time. Is there a faster way of deleting. We
> seem to continuely run out of disk space.
> Urgent!!!!!!!!!!!|||Because we at present, time out error occur when
attempting to delete more than 1000.
>--Original Message--
>Can you tell me why you can only delete 1000 rows at a
time?
>--
>Jeff Duncan
>MCDBA, MCSE+I
>"Garry Dawkins" <anonymous@.discussions.microsoft.com>
wrote in message
>news:798401c4311f$5f08b590$a301280a@.phx.gbl...
1000[vbcol=seagreen]
>
>.
>|||I agree with Aaron. Also what criteria is in your delete command. Is the
value your deleting against indexed.
Jeff Duncan
MCDBA, MCSE+I
"Aaron Bertrand - MVP" <aaron@.TRASHaspfaq.com> wrote in message
news:%238mJPFSMEHA.628@.TK2MSFTNGP11.phx.gbl...
> I also don't understand what you mean by "we can only delete 1000 row at a
> time"... what happens when you try to delete 1001?
> I recommend halting the inserts briefly, copy the last 6 months of data
into
> a new table (e.g. SELECT INTO), reset any indexes/primary keys, drop the
old
> table, rename the new table.
> Then, set up a job that runs daily (or maybe more often) that deletes any
> data more than 6 months old (184 days is probably the safest to account
for
> 31-day months). This way your window will always be roughly six months,
and
> you won't be tripping over yourself trying to trim it down. Automate it
as
> much as possible, it's less taxing on both the system and your hair.
> However, if you are running out of disk space now, what happens when your
> volume increases and you are adding 200k, 500k, 1m rows per day?
> --
> Aaron Bertrand
> SQL Server MVP
> http://www.aspfaq.com/
>
>
> "Garry Dawkins" <anonymous@.discussions.microsoft.com> wrote in message
> news:798401c4311f$5f08b590$a301280a@.phx.gbl...
>sql