if OBJECT_ID('groups') is not null

    drop table groups

create table groups

(

    groupid varchar(10),

    val int null

)

insert into groups values('a','');

insert into groups values('a','');

insert into groups values('a','');

insert into groups values('b','');

insert into groups values('b','');

insert into groups values('b','');

--临时表

if OBJECT_ID('#tmp') is not null

drop table #tmp

go

create table #tmp

(

    groupid varchar(10),

    val int null

)

insert into #tmp select * from groups where groupid='b'

select * from #tmp

drop table #tmp

go

--临时表2

select * into #tmp from groups where groupid ='b'

select * from #tmp

drop table #tmp

go

--表变量

declare @t table(id varchar(10),val int)

insert into @t select * from groups where groupid='b'

select * from @t;

--CTE

with tb(id,value) as --alias column name

(

    select * from groups where groupid='b'

)

select * from tb

--游标

if OBJECT_ID('#tmp') is not null

drop table #tmp

go

create table #tmp

(

    groupid varchar(10),

    val int null

)

declare @id varchar(10), @val  int

declare c cursor fast_forward for select * from groups where groupid='b'

open c

fetch next from c into @id,@val

while @@FETCH_STATUS =0

begin

    insert into #tmp values(@id,@val)

    fetch next from c into @id,@val

end

close c

deallocate c

select * from #tmp