请教如何删除临时表的列和从其它表中导入单独一列的数据

[xej]

create table #ta(id int,amount int)

create table #tb(id int,amount1 int)

insert into #ta (id,amount) values (1,20)

insert into #ta (id,amount) values (2,30)

insert into #ta (id,amount) values (3,50)

insert into #tb (id,amount1) values (1,20)
insert into #tb (id,amount1) values (2,50)
insert into #tb (id,amount1) values (3,40)
insert into #tb (id,amount1) values (4,70)

select * from #ta
select * from #tb

drop table #ta
drop table #tb

这里是两个临时,本人想实现两步。
1、删除临时表#ta的amount列。本人试过用这条语句alter table #ta drop column amount，但是没有成功，开始以为里面有数据，但变为空后仍然没有成功。
2、在临时表#ta的后面增加b列之后,代码（alter table #ta add b int）。本人想将#tb表中的amount1的数据插入这个新列b中，试过用exec('update #ta set b=(select amount1 from #tb)')这段和其它些代码，但是仍然没有成功。
请教各位如何实现这两步

[OracleRoob]

--1、需要加go
create table #ta(id int,amount int)
create table #tb(id int,amount1 int)
go
insert into #ta (id,amount) values (1,20)
insert into #ta (id,amount) values (2,30)
insert into #ta (id,amount) values (3,50)

insert into #tb (id,amount1) values (1,20)
insert into #tb (id,amount1) values (2,50)
insert into #tb (id,amount1) values (3,40)
insert into #tb (id,amount1) values (4,70)

select * from #ta
select * from #tb

alter table #ta drop column amount
go
select * from #ta
select * from #tb

drop table #ta
drop table #tb

[OracleRoob]

--2、加go

create table #ta(id int,amount int)
create table #tb(id int,amount1 int)
go
insert into #ta (id,amount) values (1,20)
insert into #ta (id,amount) values (2,30)
insert into #ta (id,amount) values (3,50)

insert into #tb (id,amount1) values (1,20)
insert into #tb (id,amount1) values (2,50)
insert into #tb (id,amount1) values (3,40)
insert into #tb (id,amount1) values (4,70)

select * from #ta
select * from #tb

alter table #ta drop column amount
go
alter table #ta add b int
go
update t1 set b=t2.amount1 from #ta t1 inner join #tb t2 on t1.id=t2.id

select * from #ta
select * from #tb

drop table #ta
drop table #tb

[simonhehe]

楼上正解

[xej]

wangtiecheng(不知不为过，不学就是错！)的回复虽然没错。但是，这是一个存储过程中的一段代码，存储过程中有begin...end或其它代码时就不行了。例如
declare @direction int
set @direction=1
if @direction=1
begin
create table #ta(id int,amount int)
create table #tb(id int,amount1 int)
go
insert into #ta (id,amount) values (1,20)
insert into #ta (id,amount) values (2,30)
insert into #ta (id,amount) values (3,50)

insert into #tb (id,amount1) values (1,20)
insert into #tb (id,amount1) values (2,50)
insert into #tb (id,amount1) values (3,40)
insert into #tb (id,amount1) values (4,70)

select * from #ta
select * from #tb

alter table #ta drop column amount
go
alter table #ta add b int
go
update t1 set b=t2.amount1 from #ta t1 inner join #tb t2 on t1.id=t2.id

select * from #ta
select * from #tb

drop table #ta
drop table #tb
end
请教如这段代码是在一个存储过程中间插入的该如何实现

[xeqtr1982]

alter proc ceshi
as

declare @direction int
set @direction=1

if @direction=1
begin
create table #ta(id int,amount int)

insert into #ta values (1,20)
insert into #ta values (2,30)
insert into #ta values (3,50)

create table #tb(id int,amount1 int)

insert into #tb values (1,20)
insert into #tb values (2,50)
insert into #tb values (3,40)
insert into #tb values (4,70)

select * from #ta
select * from #tb

exec('alter table #ta drop column amount')
exec('alter table #ta add b int')
exec('update t1 set b=t2.amount1 from #ta t1 inner join #tb t2 on t1.id=t2.id')

select * from #ta
select * from #tb

drop table #ta
drop table #tb
end
go

exec ceshi

[OracleRoob]

存储过程内部不能使用go，必须使用动态SQL语句添加或删除列。

而这些临时表在动态SQL中添加的列，在执行完exec后，不能在外部访问这些列。

所以，只能在Exec()内部调用update语句。

如果外部此时调用：select b from #ta会出错。

就像楼上所述即可：

exec('alter table #ta drop column amount')
exec('alter table #ta add b int')
exec('update t1 set b=t2.amount1 from #ta t1 inner join #tb t2 on t1.id=t2.id')

或

exec ('alter table #ta drop column amount alter table #ta add b int')
exec ('update t1 set b=t2.amount1 from #ta t1 inner join #tb t2 on t1.id=t2.id')

[OracleRoob]

中文也可以。

alter proc ceshi
as

declare @direction int
set @direction=1

if @direction=1
begin
create table #ta(id int,借方 int)

insert into #ta values (1,20)
insert into #ta values (2,30)
insert into #ta values (3,50)

create table #tb(id int,amount1 int)

insert into #tb values (1,20)
insert into #tb values (2,50)
insert into #tb values (3,40)
insert into #tb values (4,70)

select * from #ta
select * from #tb

exec('alter table #ta drop column 借方')
exec('alter table #ta add b int')
exec('update t1 set b=t2.amount1 from #ta t1 inner join #tb t2 on t1.id=t2.id')

select * from #ta
select * from #tb

drop table #ta
drop table #tb
end
go

exec ceshi