Database

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

Rafael Carmo wrote:

Hi!!

I'm having a problem( again) while creating a .dbf file. First, I have this line to create the table:
Create Table Results.dbf (BuyDate Date)

And this worked well.

Now, I've this line to add a date in the table:
Insert Into Results.dbf (20070922)

But it gives me a "Data type mismacth" error. I've already saw that the date format is YYYYMMDD. So where is the error here??

Snaider wrote:

Just only ASCII text?? So why is there a Date format for .dbf file...??? I tried to insert '20070922', "20070922" and this didn't work as well...

laziale wrote:

I don't know exactly where that record is, in which table. So, cause I am a beginner in SQL server, with the knowledge what I have, I did this:
SELECT * FROM dbTest WHERE VALUES ='some value';

and it gives me error, near VALUES, so I don't know how to find it,