【SqlServer】SqlServer创建临时表存入别的表中的数据及字段值

1.简单的创建临时表

drop table #tmp --删除临时表
--创建临时表
create table #tmp (
ID VARCHAR(80) not null,
PNAME VARCHAR(200)
)
INSERT INTO #tmp(ID,PNAME) SELECT ID,CNAME as PNAME from ORG_DEPARTMENT; --把别的表数据存入临时表
SELECT * FROM #tmp;--查询临时表数据
// 创建临时表的另一种方式
drop table #tmpStudent
select * into #tmpStudent from ES_NOTICE_TARGET
select * from #tmpStudent


2.相对复杂的创建临时表 更新字段数据

BEGIN --开始
drop table #tmp --删除临时表 可以不要 看需求了 不能删一个在建一个 是不是很影响效率
--创建临时表
create table #tmp (
ID VARCHAR(80) not null,
ZCOUNT VARCHAR(200),
WCOUNT VARCHAR(200),
YCOUNT VARCHAR(200)
)
--找到别表数据据插入临时表中
INSERT INTO #tmp(ID,ZCOUNT) SELECT NOTICE_ID AS ID,COUNT(DEPT_ID) AS ZCOUNT from ES_NOTICE_TARGET GROUP BY NOTICE_ID;
--更新临时表字段值 按条件更新
update #tmp set WCOUNT=(SELECT COUNT(READS_IS_OK) as WCOUNT from ES_NOTICE_TARGET where #tmp.ID=NOTICE_ID AND (READS_IS_OK='0' OR READS_IS_OK IS NULL) GROUP BY NOTICE_ID)
where EXISTS(SELECT NOTICE_ID AS ID from ES_NOTICE_TARGET GROUP BY NOTICE_ID)
update #tmp set YCOUNT=(SELECT COUNT(READS_IS_OK) as WCOUNT from ES_NOTICE_TARGET where #tmp.ID=NOTICE_ID AND READS_IS_OK='1' GROUP BY NOTICE_ID)
where EXISTS(SELECT NOTICE_ID AS ID from ES_NOTICE_TARGET GROUP BY NOTICE_ID)
SELECT * FROM #tmp; --查询临时表数据
END --结束
GO --go