CODEWITHSUNDEEP
c#asp.netmysqlsql
Vijaychandar
Vijaychandar

Reputation: 714

How to get the newly added records

In my application, i want to show the newly added RECORDS by an import operation in a gridview. Is there is any method in sql to retrive newly added rows.

I tried to do it in using code and tried to get the difference before and after the insertion and its working perfectly but makes the application very slow. So, i want to do it in database itself.

Im using Mysql, ASP.NET.

Eg: table may have these records before the import operation

ID Name
1  A
2  B
3  C

and after import the table may be like this.

ID Name
1  A
2  B
3  C
4  D
5  E
6  F

I want result like 

ID Name
4  D
5  E
6  F

Upvotes: 2

Views: 538

Answers (3)

famousgarkin
famousgarkin

Reputation: 14126

In case you use auto incremental IDs for your records, you can use:

SELECT * FROM [table] ORDER BY [id column] DESC LIMIT [number of records]

Otherwise you should add a TIMESTAMP colum to your records for this purpose and select by this column.

Personally, if there is an option, I wouldn't use the record IDs for this, as it is not what they are for. Record IDs can change throughout the lifetime of an application and they don't necessarily represent the order in which the items were added. Especially in data import/export scenarios. I'd prefer to create special columns to store such information, e.g. "CreatedAt", "ModifiedAt".

Upvotes: 0

Vijaychandar
Vijaychandar

Reputation: 714

i think this will be more simple:

SELECT MAX(id) INTO @Max_table_Id FROM table;

// Insert operation here//////

SELECT * FROM table WHERE id>@Max_table_Id;

Upvotes: 0

Omesh
Omesh

Reputation: 29111

You need to have AUTO_INCREMENT column defined on table or alternatively you can use TIMESTAMP field to retrieve newly added records, try this:

SELECT * 
FROM table_name 
ORDER BY id DESC 
LIMIT 10;

For single row insert you can use LAST_INSERT_ID after you INSERT query:

SELECT LAST_INSERT_ID();

For multi-row insert you can follow these steps:

START TRANSACTION;

SELECT MAX(id) INTO @var_max_id FROM table_name;

INSERT INTO table_name VALUES(..),(..),...;

SELECT MAX(id) INTO @var_max_id_new FROM table_name;

COMMIT;

SELECT * 
FROM table_name 
WHERE id BETWEEN (@var_max_id + 1) AND @var_max_id_new;

Upvotes: 4

Related Questions