检索sql server中多个表中最后插入的行 [英] retrieve the last inserted rows in multiple tables in sql server

问题描述

大家好



有没有办法检索sql server中多个表中最后插入的行？



i使用数据类型= datetime创建timestamp列并使其默认值=插入时间

在表1到表7的每个表中使用此查询：



ALTER TABLE [dbo]。[表1] ADD DEFAULT（getdate（））FOR [timestamp]



然后我创建了在每个表中查询

以获取每个表中最新插入的行：



SELECT TOP 1 * FROM [TABLE1] order by [timestamp ] desc

它返回正确的值



然后为了检索所有七个表中最后3个插入的行，我创建了查询：

 选择  top （ 3 ）* 来自 
（ SEL ECT   TOP   1  COL1，COL2，COL3，[ timestamp ]  FROM  TABLE1  order   by  [ timestamp ]  desc  
  union  
  SELECT   TOP   1  COL1，COL2，COL3，[ timestamp ]  FROM  TABLE2 订单 按 [ timestamp ]  desc  
  union  
  SELECT   TOP   1  COL1，COL2，COL3，[时间stamp ]  FROM  TABLE3  order   by  [ timestamp ]  desc  
  union  
  SELECT   TOP   1  COL1，COL2，COL3，[ timestamp ]  FROM  TABLE4 订单 按 [ 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 订单 按 [ timestamp ]  desc  
  union  
  SELECT   TOP   1  COL1，COL2，COL3，[ timestamp ]  FROM  TABLE7 订单 按 [ timestamp ]  desc  
）X 
  O RDER   BY   timestamp   DESC  

但出现错误消息：从字符串转换日期时转换失败。 ??

有什么问题？

解决方案

如果你想要最新的 ID 插入表格， ID 是 INDENTITY [ ^ ]专栏，使用 @@ IDENTITY [ ^ ]。

如果您想获得MAX，请使用：

  SELECT   COALESCE （MAX（ID），  1 ） AS  MaxOfID 
  FROM  TableName 

注： COALESCE功能 [ ^ ]用于将NULL替换为默认值（在本例中为 1 ）。

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??

解决方案

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:

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).

这篇关于检索sql server中多个表中最后插入的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！