Insert from daily table to history table

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

General SQL Server Forums

New to SQL Server Programming

Insert from daily table to history table

Author

Topic

Aim9b
Starting Member

3 Posts

Posted - 2011-01-09 : 20:10:18

I have a daily table of about 800 rows and a history table with a matching format (about 12 fields) in the thousands. Rows contain location, date/time, and 10 other fields. I can insert just fine, but my history table grows too fast. I would like to know how to ONLY insert the daily records when...

1- the location does NOT already exist,

OR

2- any one of the OTHER fields (3-10) change. The date/time field ALWAYS changes. I'm new to SQL & can get parts of this working, but can't quite get it all together. Thanks.

Robert H.

maevr
Posting Yak Master

169 Posts

Posted - 2011-01-10 : 01:27:44

You should be able to solve this by checking the unique key and the column for date/time in the whereclause. Only insert the rows that does not exist and the one that exists but the date/time differs.

Aim9b
Starting Member

3 Posts

Posted - 2011-01-10 : 07:05:56

Thanks for the reply, I have to ignore Date & Time because they always differ, I only want to insert where no record exists, or where one or more of the other fields changed.

Here's a condensed version of what I have so far...


ALTER            PROCEDURE Import_Daily_Data 
/*****************************************************************************/
/* DESCRIPTION:    Bulk import text file, then update history file with 
/*                 records that have any significant field changes
/* ASSUMPTIONS:    The comma delimited text file exists.
/*****************************************************************************/
AS
   TRUNCATE TABLE Daily_Data
   BULK INSERT Dialy_Data
   FROM 'C:\myfile.txt'
   WITH   ( FIELDTERMINATOR = ',', ROWTERMINATOR = '\n' )
 
  INSERT Hist(
    ph_Location, ph_DateTime,
    ph_OS_Ver, ph_OS_VerDate, ph_IPP_SerNum, ph_IPP_Ver) 
    SELECT pd_Location, pd_DateTime,
      pd_OS_Ver, pd_OS_VerDate, pd_IPP_SerNum, pd_IPP_Ver 
      FROM Daily_Data pd
      RIGHT JOIN Hist 
        ON  pd_Location = ph_Location
    WHERE pd_Location = ph_Location
     OR  pd_OS_Ver     <> ph_OS_Ver
     OR  pd_OS_VerDate <> ph_OS_VerDate 
     OR  pd_IPP_SerNum <> ph_IPP_SerNum 
     OR  pd_IPP_Ver    <> ph_IPP_Ver

Robert H.

dataguru1971
Master Smack Fu Yak Hacker

1464 Posts

Posted - 2011-01-10 : 09:47:00

How about a MERGE (SQL 2008)

MERGE into HIST AS TARGET
USING Daily_Data as Source
ON SOURCE_Location = TARGET.Location
AND SOURCE.pd_OS_Ver = TARGET.ph_OS_Ver
AND SOURCE.pd_OS_VerDate = TARGET.ph_OS_VerDate
AND SOURCE.pd_IPP_SerNum = TARGET.ph_IPP_SerNum
AND SOURCE.pd_IPP_Ver = TARGET.ph_IPP_Ver
WHEN NOT MATCHED
INSERT ( ph_Location, ph_DateTime,
ph_OS_Ver, ph_OS_VerDate, ph_IPP_SerNum, ph_IPP_Ver)
VALUES (pd_Location, pd_DateTime,
pd_OS_Ver, pd_OS_VerDate, pd_IPP_SerNum, pd_IPP_Ver)

Poor planning on your part does not constitute an emergency on my part.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-10 : 10:53:00

i think equivalent MERGE is below



MERGE into HIST AS TARGET
USING Daily_Data as Source
ON SOURCE_Location = TARGET.Location 
WHEN NOT MATCHED
INSERT ( ph_Location, ph_DateTime,
ph_OS_Ver, ph_OS_VerDate, ph_IPP_SerNum, ph_IPP_Ver)
VALUES (SOURCE.pd_Location, SOURCE.pd_DateTime,
SOURCE.pd_OS_Ver, SOURCE.pd_OS_VerDate, SOURCE.pd_IPP_SerNum, SOURCE.pd_IPP_Ver)
WHEN MATCHED 
AND ( TARGET.pd_OS_Ver     <> SOURCE.ph_OS_Ver
     OR  TARGET.pd_OS_VerDate <> SOURCE.ph_OS_VerDate 
     OR  TARGET.pd_IPP_SerNum <> SOURCE.ph_IPP_SerNum 
     OR  TARGET.pd_IPP_Ver    <> SOURCE.ph_IPP_Ver   
)
THEN UPDATE SET pd_OS_Ver     = SOURCE.ph_OS_Ver,
     pd_OS_VerDate = SOURCE.ph_OS_VerDate ,
     pd_IPP_SerNum = SOURCE.ph_IPP_SerNum, 
     pd_IPP_Ver    = SOURCE.ph_IPP_Ver

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Aim9b
Starting Member

3 Posts

Posted - 2011-01-10 : 19:48:02

Thank you visakh16 & dataguru1971. Unfortunately I don't know enough to know if these will work, but I'm anxious to try them. I think we are on SQL 2000 or 2003. We're heading for 2008, but could be 6-12 months & I can't wait for it.

visakh16, does your MERGE solution require 2008 as well? Thank you.

Someone here suggested I need to retrieve the LATEST row by ph_DateTime (there can be many) from the History table, for each Location in the Daily Table, in order to compare the rest of the fields. Then if any have changed, I DO NOT want to UPDATE this row, but INSERT the Daily row AS IT EXISTS, thus making an even LATER dated entry to test against tomorrow's Daily Table. Does MERGE do this? If MERGE(2008) is not available to me, what is my next best choice.

Thanks Again, for all your patience.

Robert H.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-01-11 : 11:02:25

as per your latest explanation, i think what you're looking at is this?


INSERT Hist(
    ph_Location, ph_DateTime,
    ph_OS_Ver, ph_OS_VerDate, ph_IPP_SerNum, ph_IPP_Ver) 
    SELECT pd_Location, pd_DateTime,
      pd_OS_Ver, pd_OS_VerDate, pd_IPP_SerNum, pd_IPP_Ver 
      FROM Daily_Data pd
      INNER JOIN Hist 
        ON  pd_Location = ph_Location
    WHERE pd_OS_Ver     <> ph_OS_Ver
     OR  pd_OS_VerDate <> ph_OS_VerDate 
     OR  pd_IPP_SerNum <> ph_IPP_SerNum 
     OR  pd_IPP_Ver    <> ph_IPP_Ver

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources