select top 1 id into #test from aaaa where type=1 order by getdate desc这样给临时表插入一条信息后,就不能再向临时表#test中追加信息了么?
比如
select top 1 id into #test from aaaa where type=2 order by getdate desc这样就会出错,为什么啊
比如
select top 1 id into #test from aaaa where type=2 order by getdate desc这样就会出错,为什么啊
select top 1 id from aaaa where type=1 order by getdate desc
insert into aaa (test) values('')
我把存储过程改诚这样了
select top 1 id into #test from aaaa where type=1 order by getdate desc
insert into #test select id from aaaa where type=2 order by getdate desc但这样会报错啊仅当使用了列的列表,并且 IDENTITY_INSERT 为 ON 时,才能在表 '#test' 中为标识列指定显式值。
这个是创建表并插入,再这样,由于表已经存在就会出错,你可以use tempdb
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[#test]
use userdb --你的库
select top 1 id into #test from aaaa where type=1 order by getdate desc或
use tempdb
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[#test]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
begin
use userdb --你的库
insert into.............
end
else
begin
use userdb --你的库
select top 1 id into #test from aaaa where type=1 order by getdate desc
end
if exists (select * from tempdb.dbo.sysobjects where id = object_id(N'tempdb.dbo.#test') )
但又担心怕不准..........
要不然可以减少上面的use...了仅当使用了列的列表,并且 IDENTITY_INSERT 为 ON 时,才能在表 '#test' 中为标识列指定显式值
-------------->select ..into #test后,表结构也创建了,id为标识列,不能再插入修改所以这又是个问题,插入前得修改SET IDENTITY_INSERT ON
我主要是想返回几条记录
就是TYPE=1的第一条记录,TYPE=2的第一条记录.........
这样
有什么更好的办法么
如果只是type=1 和type=2
你怎么不用 select id into #test from aaaa where type in(1,2)
group by type
order by getdate desc
就是都是top 1
where type=1 or type=2
group by type
order by getdate desc
没试验