Database

Vuyiswa wrote:

And this Points to this line

Open Cursor Find_Val

delete 
from tableB
where exists
(select top 1 * from tableA
where keyvalue = tableB.keyvalue)

--declare and initialize variables
DECLARE @publisher varchar(25);
SET @publisher = 'mypublisher';
DECLARE @dateStart datetime;
DECLARE @dateEnd datetime;
SET @dateStart = '2008-02-27 00:00:00.000';
SET @dateEnd = '2008-03-01 23:59:59.999';
declare @byteChunk int
set @byteChunk = 2;
DECLARE @sizeDivider bigint;
SET @sizeDivider =  CASE (@byteChunk)
                        WHEN 0 THEN 1024
                        WHEN 1 THEN 1048576
                        WHEN 2 THEN 1073741824
                        WHEN 3 THEN 1099511627776
                        WHEN 4 THEN 1125899906842624
                    END;

--create our temporary tables
CREATE TABLE #tempB
(
    DownloadDate datetime,
    Bytes int,
    PublisherName varchar(50),
    UniqueKey varchar(80)
)
CREATE TABLE #tempC
(
    DownloadDate datetime,
    Bytes int,
    PublisherName varchar(50),
    UniqueKey varchar(80)
)

-- gather all of the appropriate records from another table, and put
-- them into a temporary table
insert into #tempB (DownloadDate, Bytes, PublisherName, UniqueKey)
select CAST(FLOOR(CAST(EndTimeStamp as float)) as DateTime) as endtimestamp, TotalBytesReceived, PublisherName, Convert(varchar, CAST(FLOOR(CAST(EndTimeStamp as float)) as DateTime)) + '.' + @publisher as UniKey
from dbo.downloads
where LOWER(publishername) = LOWER(@publisher)
and EndTimeStamp between @dateStart and @dateEnd
order by endtimestamp;

-- transfer only unique records to a second temporary table (records that
-- don't already exist in exist in dailybandwidth) - we're trying to side-step
-- the error handling in SQL and effectively let the Stored proc insert what it
-- can and ignore the duplicate records
insert into #tempC (DownloadDate, Bytes, PublisherName, UniqueKey)
SELECT DownloadDate, Bytes, PublisherName, UniqueKey
FROM #TEMPB
where not exists
(
    SELECT DownloadDate, Bytes, PublisherName, UniqueKey
    FROM dailybandwidth
    where #tempb.uniquekey = dailybandwidth.uniquekey
)

-- transfer records from 2nd temp table (guaranteed to be unique)
-- into destination table
insert into dbo.DailyBandwidth (DownloadDate, Bytes, PublisherName, UniqueKey)
select DownloadDate, SUM((Bytes*1.0) / @sizeDivider) as Bytes, PublisherName, UniqueKey
from #tempC
group by DownloadDate, Publishername, UniqueKey
order by DownloadDate asc

-- drop our temp tables
drop table #tempB
drop table #tempc