find similar records and merge them into one record

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Import/Export (DTS) and Replication (2000)

find similar records and merge them into one record

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2002-08-14 : 08:37:33

Jim writes "I have two ftp logs one Linux, one ServU to get into
one database. I can get them in just fine...and use
count () to get hitcounts on my files...problem is
that in loading the two log files I have two records
with "date" "filename" and "hitcount" that are identical.
Unfortunately the text format of the two logs are
extremely different..so merging the two logs before
importing them is a nightmare

isn't there some way to go thru the database...look for
where "date" "filename" match and then sum the "hitcount"
field......delete the two records and create one with
the true count?

thanks in advance..."

robvolk
Most Valuable Yak

15732 Posts

Posted - 2002-08-14 : 09:36:07

1. Create 3 tables:

CREATE TABLE LinuxLog (FileDate datetime, FileName varchar(128), Hits int)
CREATE TABLE ServULog (FileDate datetime, FileName varchar(128), Hits int)
CREATE TABLE AllLogs (FileDate datetime, FileName varchar(128), Hits int)

2. Import the Linux and ServU log files into their respective tables. Change the table structures to match the log file formats as necessary, as long as the three columns they share are present in both.

3. INSERT the combined data into the AllLogs table like so:

INSERT INTO AllLogs (FileDate, FileName, Hits)
SELECT A.FileDate, A.FileName, Sum(A.Hits) AS Hits
FROM (SELECT FileDate, FileName, Hits FROM LinuxLog
UNION ALL
SELECT FileDate, FileName, Hits FROM ServULog) AS A
GROUP BY A.FileDate, A.FileName

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources