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 :
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??
Submit an article or tip