因为下面的语句二查询被反复执行,因此我这么写
select * into ##t from t --语句一 , 读数据到内存select ID from ##t ---语句二, 查询现在的问题是,如何修改第一条语句,以完成当t表有##t没有的新数据时更新##t,或t表里的数据更新后也更新##t使得在语句二执行前两个表的数据一致?
select * into ##t from t --语句一 , 读数据到内存select ID from ##t ---语句二, 查询现在的问题是,如何修改第一条语句,以完成当t表有##t没有的新数据时更新##t,或t表里的数据更新后也更新##t使得在语句二执行前两个表的数据一致?
select ID from t ?
insert into ##t
select * from t where not exists(select * from ##t where ##t.id=t.id)
go
create table ##t1 (id int primary key, data char(1))
gocreate trigger tr_test_t1 on t1 for insert,update,delete
as
set nocount on
if object_id('tempdb.dbo.##t1') is null return
if not exists (select 1 from inserted)
delete ##t1 from ##t1 a inner join deleted b on a.id = b.id
else if not exists (select 1 from deleted)
insert ##t1 select * from inserted
else
update ##t1 set data = b.data from ##t1 a inner join inserted b on a.id = b.id
set nocount off
goinsert t1 select 1,'a'
insert t1 select 2,'b'
select * from t1
select * from ##t1
goupdate t1 set data = 'c' where id = 1
select * from t1
select * from ##t1
godelete from t1 where id =2
select * from t1
select * from ##t1
godrop table t1,##t1
什么时候键?如果是个表还可以
if object_id('tempdb.dbo.##t1') is null return
....你什么时候建都可以,建的时候初始化就可以了:
select * into ##t1 from t1你或许会说##t1会冲突,那只能弄个复杂点的名字。或者你自己建一个保留字库,开发时候不能用保留的字