--用动态语句 SELECT * INTO #TMP FROM ENG_MST WHERE USED='Y' AND IDCARD IS NOT NULL ALTER TABLE TEMPDB..#TMP ADD INS VARCHAR(10) NULLexec('UPDATE #TMP SET ENG_ID=@ENG_ID,III=''Y'' WHERE [ENG_ID]='+@ID)
create table t(id int) goinsert into t select 1 insert into t select 2 insert into t select 3 goselect * into # from t exec('alter table # add rq varchar(10)') update # set rq='2005-01-01' where id=1 select * from #drop table t,#
drop table #TMP go SELECT * INTO #TMP FROM ENG_MST WHERE USED='Y' AND IDCARD IS NOT NULL go ALTER TABLE #TMP ADD INS VARCHAR(10) NULL go UPDATE #TMP SET ENG_ID=@ENG_ID,INS='Y' WHERE [ENG_ID]=@ID go这样有问题吗?
先告诉这一句中的 @ENG_ID和@ID是怎么来的,说不定是他们在作怪~
--这里不要指定tempdb,直接用#tmp即可select * into #tmp from eng_mst where used='y' and idcard is not null alter table [tempdb..#tmp] add ins varchar(10) null-->select * into #tmp from eng_mst where used='y' and idcard is not null alter table #tmp add ins varchar(10) null
SELECT * INTO #TMP FROM ENG_MST WHERE USED='Y' AND IDCARD IS NOT NULL
ALTER TABLE TEMPDB..#TMP ADD INS VARCHAR(10) NULLexec('UPDATE #TMP SET ENG_ID=@ENG_ID,III=''Y'' WHERE [ENG_ID]='+@ID)
goinsert into t select 1
insert into t select 2
insert into t select 3
goselect * into # from t
exec('alter table # add rq varchar(10)')
update # set rq='2005-01-01' where id=1
select * from #drop table t,#
go
SELECT * INTO #TMP FROM ENG_MST WHERE USED='Y' AND IDCARD IS NOT NULL
go
ALTER TABLE #TMP ADD INS VARCHAR(10) NULL
go
UPDATE #TMP SET ENG_ID=@ENG_ID,INS='Y' WHERE [ENG_ID]=@ID
go这样有问题吗?
@ENG_ID和@ID是怎么来的,说不定是他们在作怪~
--这里不要指定tempdb,直接用#tmp即可select * into #tmp from eng_mst where used='y' and idcard is not null
alter table [tempdb..#tmp] add ins varchar(10) null-->select * into #tmp from eng_mst where used='y' and idcard is not null
alter table #tmp add ins varchar(10) null
以下内容摘自BOL:
如果本地临时表由存储过程创建或由多个用户同时执行的应用程序创建,则 SQL Server 必须能够区分由不同用户创建的表。为此,SQL Server 在内部为每个本地临时表的表名追加一个数字后缀。存储在 tempdb 数据库的 sysobjects 表中的临时表,其全名由 CREATE TABLE 语句中指定的表名和系统生成的数字后缀组成。为了允许追加后缀,为本地临时表指定的表名 table_name 不能超过 116 个字符。
这样写不对吗?
IF EXISTS(SELECT 1 FROM TEMPDB..SYSOBJECTS WHERE ID=1('tempdb..#TMP')AND XTYPE='U')
DROP TABLE #TMP
这样写不对吗?
IF EXISTS(SELECT 1 FROM TEMPDB..SYSOBJECTS WHERE ID=1('tempdb..#TMP')AND XTYPE='U')
DROP TABLE #TMP我晕请看清我提问的问题呀@!
在一个新的连接中,没有必要判断是否存在与要创建的临时表同名的临时表,因为一旦此会话结束后,临时表自动就清除了。