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,

laziale wrote:

but that database has around 30 tables, and lots of records inside, so any help on some query, to find the text, like 'sometext' will be helpfull. Thanks for replying

laziale wrote:

the value is 'noordzeeweg' but the think is that we don't know exactly in which table or column exactly is the record, and we have around 30 tables with lots of columns and records inside.