retrieve the last inserted rows in multiple tables in sql server

Question

0.00/5 (No votes)

See more:

hi all

is there any way to retrieve the last inserted rows in multiple tables in sql server ??

i create the timestamp column with data type = datetime and make its default value = the time inserted
using this query in each table from table1 to table 7 :

ALTER TABLE [dbo].[Table 1] ADD DEFAULT (getdate()) FOR [timestamp]

then i create the query
in each table to get the latest inserted row in each table :

SELECT TOP 1 * FROM [TABLE1] order by [timestamp] desc
and it returns the correct value

then in order to retrieve the last 3 inserted rows in all seven tables , i create the query :

SQL

select top (3) * from
(SELECT TOP 1 COL1 ,COL2 ,COL3,[timestamp] FROM TABLE1 order by [timestamp] desc
union
SELECT TOP 1 COL1 ,COL2 ,COL3,[timestamp] FROM TABLE2 order by [timestamp] desc
union
SELECT TOP 1 COL1 ,COL2 ,COL3,[timestamp] FROM TABLE3 order by [timestamp] desc
union
SELECT TOP 1 COL1 ,COL2 ,COL3,[timestamp] FROM TABLE4 order by [timestamp] desc
union
SELECT TOP 1 COL1 ,COL2 ,COL3,[timestamp] FROM TABLE5 order by [timestamp] desc
union
SELECT TOP 1 COL1 ,COL2 ,COL3,[timestamp] FROM TABLE6 order by [timestamp] desc
union
SELECT TOP 1 COL1 ,COL2 ,COL3,[timestamp] FROM TABLE7 order by [timestamp] desc
) X
ORDER BY timestamp DESC

but the error message appears : "Conversion failed when converting datetime from character string." ??
what is the problem??

Posted 30-Apr-14 21:05pm

Sarah MQ

Updated 2-May-14 3:35am

v2

Add a Solution

Comments

RahulMGunjal 1-May-14 4:35am

Do you want to know last row inserted in each table or last row inserted in any table in a database?

Amalraj Ramesh 2-May-14 0:06am

Use scope_identity function

Sarah MQ 2-May-14 0:54am

i want to know the last inserted 3 row in 7 tables in the same database

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Maciej Los · Answer 1 · 2014-05-01T12:01:00

Solution 1

If you want the latest value of ID inserted into table and ID is a INDENTITY[^] column, use @@IDENTITY[^].
If you would like to get MAX, use:

SQL

SELECT COALESCE(MAX(ID),1) AS MaxOfID
FROM TableName

Note: COALESCE function[^] were used to replace NULL with default value (in this case with 1).

Posted 1-May-14 12:01pm

Maciej Los

Comments

Sarah MQ 2-May-14 8:48am

select top (3) * from
(SELECT TOP 1 COL1 ,COL2 ,COL3,[timestamp] FROM TABLE1 order by [timestamp] desc
union
SELECT TOP 1 COL1 ,COL2 ,COL3,[timestamp] FROM TABLE2 order by [timestamp] desc
union
SELECT TOP 1 COL1 ,COL2 ,COL3,[timestamp] FROM TABLE3 order by [timestamp] desc
union
SELECT TOP 1 COL1 ,COL2 ,COL3,[timestamp] FROM TABLE4 order by [timestamp] desc
union
SELECT TOP 1 COL1 ,COL2 ,COL3,[timestamp] FROM TABLE5 order by [timestamp] desc
union
SELECT TOP 1 COL1 ,COL2 ,COL3,[timestamp] FROM TABLE6 order by [timestamp] desc
union
SELECT TOP 1 COL1 ,COL2 ,COL3,[timestamp] FROM TABLE7 order by [timestamp] desc
) X
ORDER BY timestamp DESC

WHEN I EXECUTE THE QUERY THE MESSAGE "Incorrect syntax near the keyword 'union'."
i added a new column [timestamp] that have a default value = getdate()

in order to retrieve the latest 3 columns added in 7 tables
please what do you think about this issue??

Maciej Los 2-May-14 9:02am

Remove ORDER BY clause in ech subquery and replace word Union with UNION ALL.
Does [timestamp] is a table's field?
I'm not getting you... Post sample data and expected output. Use "Improve question" widget.

Sarah MQ 2-May-14 9:26am

the same message still appears ,
i will improve my question

Maciej Los 2-May-14 10:52am

I don't see sample data...