INSERT only if different from previous 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 2008 Forums

Transact-SQL (2008)

INSERT only if different from previous record

Author

Topic

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2011-04-22 : 14:59:03

I have a table ([ArtTblHist]) in which changes to my item attributes are being logged.
I need to find a way to only insert into this table from my source table when any of the item attributes have changed from the previous entry in table [ArtTblHist]:



--Create  tables
CREATE TABLE ArtTblHist
(
Article VARCHAR(8),
ArtCategory CHAR(3),
ArtStatus INT,
ArtRank INT,
DateFormatted char(10)
)

CREATE TABLE ArtTbl
(
Article VARCHAR(8),
ArtCategory CHAR(3),
ArtStatus INT,
ArtRank INT,
DateFormatted char(10)
)

--Insert records in source  table  ArtTbl
INSERT INTO ArtTbl VALUES('1103001','BAR',1,4,'2011-04-22')
INSERT INTO ArtTbl VALUES('1103002','QTR',1,12,'2011-04-22')
INSERT INTO ArtTbl VALUES('1103003','QTR',4,19,'2011-04-02')
 
--Insert records in history table  ArtTblHist
INSERT INTO ArtTblHist VALUES('1103001','BAR',1,10,'2011-03-28')
INSERT INTO ArtTblHist VALUES('1103001','BAR',1,10,'2011-03-30')
INSERT INTO ArtTblHist VALUES('1103002','QTR',1,12,'2011-03-28')
INSERT INTO ArtTblHist VALUES('1103002','CDW',2,12,'2011-03-29')
INSERT INTO ArtTblHist VALUES('1103003','QTR',1,3,'2011-03-28')
INSERT INTO ArtTblHist VALUES('1103003','QTR',3,3,'2011-03-29')
INSERT INTO ArtTblHist VALUES('1103003','QTR',1,3,'2011-03-30')
INSERT INTO ArtTblHist VALUES('1103003','QTR',4,19,'2011-04-02')

Now I would need to insert the records for articles 1103001 and 1103002 from ArtTabl into ArtTblHist but not 1103003 because the record is identical to the last record for that article in the history table.
I could retrieve the respective last entry for each article from the history table like this:


	SELECT * FROM 
		(SELECT Article,ArtCategory,ArtStatus,ArtRank,DateFormatted, Row_number() OVER (Partition by Article Order by DateFormatted DESC) as [SQN]
		 FROM ArtTblHist) list
WHERE SQN  = 1

How can I join this result set to my ArtTbl to determine which rows to inert?

Any help is appreciated.

jimf
Master Smack Fu Yak Hacker

2875 Posts

Posted - 2011-04-22 : 15:13:01

INSERT INTO #artTblhist
select *
from #arttbl
except
select * from #artTblhist

will insert all the records in #arttbl not in #artTblHist
Jim

Everyday I learn something that somebody else already knew

HenryFulmer
Posting Yak Master

110 Posts

Posted - 2011-04-22 : 15:39:44

I'm sorry I made a mistake in the data insert. The record for article 1103003 in the ArtTbl should be

INSERT INTO ArtTbl VALUES('1103003','QTR',4,19,'2011-04-22')

. That means all fields are the same for this article then for its previous earlier entry. (except for the date) I need to be able to compare the article entry in the ArtTable with the latest entry in ArtTblHist table to determine if I should insert the record from ArtTbl.

DonAtWork
Master Smack Fu Yak Hacker

2167 Posts

Posted - 2011-04-25 : 10:49:25

Sounds like a job for a trigger to me.

http://weblogs.sqlteam.com/jeffs/archive/2008/05/13/question-needed-not-answer.aspx
How to ask: http://weblogs.sqlteam.com/brettk/archive/2005/05/25/5276.aspx
For ultra basic questions, follow these links.
http://www.sql-tutorial.net/
http://www.firstsql.com/tutor.htm
http://www.w3schools.com/sql/default.asp

Sachin.Nand

2937 Posts

Posted - 2011-04-25 : 13:20:23

Is this the record which you need ?


select a.* from arttbl a
cross apply(select top 1 * from arttblhist h where h.article=a.article and h.artcategory<>a.artcategory
and a.artstatus<>h.artstatus and a.artrank=h.artrank order by dateformatted desc)o

PBUH

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources