Retrival of newly inserted rows

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

Retrival of newly inserted rows

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2006-08-09 : 09:35:21

Rajesh writes "Hi
I have a table. I need to see the newly inserted row on the day. i.e. newly inserted data alone. I dont have any date field in the table."

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-08-09 : 09:39:48

Do you use identity column?

Madhivanan

Failing to plan is Planning to fail

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2006-08-09 : 10:58:42

if you have a column that store the date & time of the insertion you can easily do it.
for example

select * 
from   yourtable
where  date_col > '20060809 11:23'

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-09 : 11:03:54

U may have a trigger to write all the stuff during each day to a different table with the GetDate() (obviously with an additional column), and view that data.

Srinika

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-08-09 : 23:13:49

Why create a new table? Why not just add the column?

Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)

7020 Posts

Posted - 2006-08-09 : 23:21:49

quote:
Originally posted by AskSQLTeam

Rajesh writes "Hi
I have a table. I need to see the newly inserted row on the day. i.e. newly inserted data alone. I dont have any date field in the table."

So you want to query based on information that does not exist?

CODO ERGO SUM

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-10 : 08:30:34

quote:
Originally posted by LoztInSpace

Why create a new table? Why not just add the column?

In case the code is written with "Select * " and assumed the # of fields r fixed, the programs using this table would start failing.

Its easy to maintain this sort of an audit table seperately as, whenever needed, some records can be deleted.

Srinika

LoztInSpace
Aged Yak Warrior

940 Posts

Posted - 2006-08-10 : 23:32:07

Add the column, rename the table and create a view with the original name of the table.
Then encourage developers to select only those columns they intend to use.

Srinika
Master Smack Fu Yak Hacker

1378 Posts

Posted - 2006-08-11 : 08:23:13

quote:
Originally posted by LoztInSpace

Add the column, rename the table and create a view with the original name of the table.
Then encourage developers to select only those columns they intend to use.

I agree with ur solution, for my first issue (ie. the issue if wild cards r used when selecting fields)

Ur approach doesn't do any good for the second scenario that I'm talking.

The Need is:

quote:
I need to see the newly inserted row on the day. i.e. newly inserted data alone.

So that only after seeing some data, data may become obsolete.

I understood this as an Audit or Analysis requirement. The question implies that there is no Date field (and I assumed that the Question raiser doesn't want to add one, because from his question it is clear that he knows, if he has a date field, he could have obtained his desired results)

That is the reason, I suggested the approach of having these records in a seperate table.

Srinika

druer
Constraint Violating Yak Guru

314 Posts

Posted - 2006-08-11 : 09:09:57

quote:
quote:
--------------------------------------------------------------------------------
Originally posted by AskSQLTeam

Rajesh writes "Hi
I have a table. I need to see the newly inserted row on the day. i.e. newly inserted data alone. I dont have any date field in the table."

--------------------------------------------------------------------------------

So you want to query based on information that does not exist?

One solution (albeit bad) would be to have a batch job run at night that deletes all of the data in the table. Then the next day you when run the query you will only see the data entered on that day.

Not liking that solution ... If the ID field is an IDENTITY datatype, then instead of requiring a trigger and a complete audit of the table to handle the date field, have another table that would be populated at 12:00 AM each night to write the ending ID value for that day. Then you could query for the range between any 2 days values.

Another fun solution might be to just return any result set you want and have the end user prove that the data in the result set wasn't entered that day. Without the date field they couldn't prove it and any result set would have to be considered correct.

So much data so little time (literally),
Dalton

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources