How to write a query to find out I Inserted records in Sql servertable

Question

How to write a query to find out I Inserted records in Sql servertable

venkat113

SSC-Addicted

Points: 420
More actions
December 16, 2011 at 11:21 pm

#391151

Scenario is
I Have one table in server database example "employee" in that first column is identity column i.e empid ,
empname ,sal like that
All users inserting records into table How to write query find out i inserted records in that table
Pls help me

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

salum SSCommitted Points: 1598 More actions · Answer 1

You can use CDC feature if you are using SQL server 2008. it will provide the data changes but it won't give , who did the change.

refer below link

http://www.mssqltips.com/sqlservertip/1474/using-change-data-capture-cdc-in-sql-server-2008/

java[/url]

salum SSCommitted Points: 1598 More actions · Answer 2

SQL Server Audit feature can help you that you want.

http://www.mssqltips.com/sqlservertip/1954/sql-server-2008-auditing-feature/

java[/url]

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 3

For this simple scenario CDC is not required. Add two audit columns in Employee table ‘Modified_By’ & ‘Modification_Date’ (optionally add 2 more columns ‘Created_By’ & ‘Creation_Date’). It should satisfy your requirements.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 4

That's not automatically tracked, SQL doesn't store that info for you anywhere, so for older records, unless you had some form of auditing there's no way you'll be able to tell that.

Going forward there are many ways to do this, especially on SQL 2008. You can use SQL Audit, Change Tracking, Change Data Capture, triggers with an audit table or simply a column with a default value of Original_Login()

I suggest you read up on the various options and see which will work better for you, they all have advantages and disadvantages.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Raunak Jhawar SSCoach Points: 15701 More actions · Answer 5

As other repondants have evidently mentioned, you can have a column such which will store the value of the SQL function ORIGINAL_LOGIN() and later use the filter cluse to retrieve the records who inserted what data

Raunak J