SQL临时表的使用

 

declare @tempTable table(id int,name nvarchar(200))

insert into @tempTable select t1.id,t1.name from table1 as t1

insert into @tempTable select t.id,t.name from (
select t2.id,t2.name from table2 as t2
inner join table1 as t1 on t2.id=t1.id
where t2.id>'2')

select * from @tempTable

实例

--创建表---

CREATE TABLE [dbo].[P_WORKS_DESIGN_TYPE](
 [ID] [int] IDENTITY(1,1) NOT NULL,
 [NAME] [varchar](200) NOT NULL,
 [LEVEL] [int] NOT NULL,
 [PARENT_ID] [int] NOT NULL,
 [STATU] [int] NOT NULL,
 [CREATE_TIME] [datetime] NOT NULL,
 [COMM] [varchar](200) NULL,
 [ICON] [nvarchar](100) NULL,
 CONSTRAINT [PK_P_WORKS_DESIGN_TYPE] PRIMARY KEY CLUSTERED
(
 [ID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

--创建表结束---

declare @selcategory table(id int,name nvarchar(200))
insert into @selcategory select b1.ID,b1.NAME from
(select a2.ID,a2.NAME from P_WORKS_DESIGN_TYPE as a1
inner join
P_WORKS_DESIGN_TYPE as a2
on a2.PARENT_ID=a1.ID
where a1.ID=89) as b1
insert into @selcategory
select b2.ID,b2.NAME from
(select a3.ID,a3.NAME from P_WORKS_DESIGN_TYPE as a1
inner join P_WORKS_DESIGN_TYPE as a2
on a2.PARENT_ID=a1.ID
inner join P_WORKS_DESIGN_TYPE as a3
on a3.PARENT_ID=a2.ID
where a1.ID=89) as b2
select * from @selcategory