Generate date log from changing date

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 2005 Forums

Transact-SQL (2005)

Generate date log from changing date

Author

Topic

DLTaylor
Posting Yak Master

136 Posts

Posted - 2009-06-26 : 04:18:59

Hello,

I have a table called #CurrentData.
I import a file each day called #NewData.

The field ‘EstimatedEndDate’ is sometimes updated by users in the #NewData – but due to the db design we have no ‘log’ of the changes (the data is just overwritten!)

I would like to create a process that logs the changes to ‘EstimatedEndDate’ so that I have a table that looks something like #CurrentDataUpdated

Any help will be most appreciated.
Thanks

---------------------------------------------
/*
Please paste T-SQL into query window and run
*/
---------------------------------------------
CREATE TABLE #CurrentData

(
CustID nvarchar(6),
ActivityStartDate [datetime] NULL,
EstimatedEndDate [datetime] NULL,
seqID int
)

GO
SET ANSI_PADDING OFF

Insert into #CurrentData
select '012345','2009-06-22 00:00:00.000','2009-07-02 00:00:00.000','1' UNION all
select '012346','2009-06-23 00:00:00.000','2009-07-14 00:00:00.000','1' UNION all
select '012347','2009-06-26 00:00:00.000','2009-08-23 00:00:00.000','1'

select * from #CurrentData

--New Imported Data
CREATE TABLE #NewData
(
CustID nvarchar(6),
ActivityStartDate [datetime] NULL,
EstimatedEndDate [datetime] NULL

)

Insert into #NewData
select '012345','2009-06-22 00:00:00.000','2009-07-20 00:00:00.000' UNION all
select '012346','2009-06-23 00:00:00.000','2009-07-14 00:00:00.000' UNION all
select '012347','2009-06-26 00:00:00.000','2009-08-20 00:00:00.000'

select * from #NewData

--Table I would like to make using a join on #CurrentData and #NewData

CREATE TABLE #CurrentDataUpdated

(
CustID nvarchar(6),
ActivityStartDate [datetime] NULL,
EstimatedEndDate [datetime] NULL,
seqID int
)

GO
SET ANSI_PADDING OFF

Insert into #CurrentDataUpdated
select '012345','2009-06-22 00:00:00.000','2009-07-02 00:00:00.000','1' UNION all
select '012345','2009-06-22 00:00:00.000','2009-07-20 00:00:00.000','2' UNION all
select '012346','2009-06-23 00:00:00.000','2009-07-14 00:00:00.000','1' UNION all
select '012347','2009-06-26 00:00:00.000','2009-08-20 00:00:00.000','1' UNION all
select '012347','2009-06-26 00:00:00.000','2009-08-23 00:00:00.000','2'

select * from #CurrentDataUpdated

drop table #CurrentData
drop table #NewData
drop table #CurrentDataUpdated

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-26 : 04:53:42

you can make use of the OUTPUT clause to output the value from INSERTED and insert into your CurrentDataUpdated table.


UPDATE	c
SET	
	ActivityStartDate	= n.ActivityStartDate,
	EstimatedEndDate	= n.EstimatedEndDate
OUTPUT 
	INSERTED.CustID, 
	INSERTED.ActivityStartDate, 
	INSERTED.EstimatedEndDate
INTO	#CurrentDataUpdated 
	(
		CustID, 
		ActivityStartDate, 
		EstimatedEndDate
	)
FROM	#CurrentData c
	INNER JOIN #NewData n	ON	c.CustID	= n.CustID

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 04:55:10

Ot just make a trigger to log changes?

E 12°55'05.63"
N 56°04'39.26"

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-06-26 : 05:04:32

Trigger was the 1st thing that came to my mind.

Just wonder .. any performance different comparing TRIGGER with the OUTPUT CLAUSE method ? When you have a large sets of data to insert / update such as in this case.

Peso, ever done any comparison on this ?

KH
[spoiler]Time is always against us[/spoiler]

SwePeso
Patron Saint of Lost Yaks

30421 Posts

Posted - 2009-06-26 : 05:32:41

The OUTPUT solution needs a rewrite of existing code.
TRIGGER does not.

Also, TRIGGER also captures changes done directly to table via SSMS, and OUTPUT does not.

E 12°55'05.63"
N 56°04'39.26"

DLTaylor
Posting Yak Master

136 Posts

Posted - 2009-06-26 : 06:45:44

Well, i dont think i can add much to your debate over best methods :-)

Using the advice i have ended up using the OUTPUT clause. and it is now working in test!

This was good for me as id never seen this option before.

Thanks
....i haven’t run any comparisons against the TRIGGER method :-)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources