号外号外: 原[图享网]更名为 码友网(codedefault.com) 啦,感谢大家一路上的陪伴与支持。代码的世界里,码友网与大家一起同行!

[SQL Server]SQL Server数据库中判断临时表是否存在并删除后创建的方法有哪些?

SQL Server 作者: Rector 282阅读 0评论 0收藏 收藏本文

郑重申明:本文未经许可,禁止任何形式转载

[SQL Server]SQL Server数据库中判断临时表是否存在并删除后创建的方法有哪些?

方案一

IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
GO
CREATE TABLE #Results ( Company CHAR(3), StepId TINYINT, FieldId TINYINT )
GO
select company, stepid, fieldid from #Results
GO
ALTER TABLE #Results ADD foo VARCHAR(50) NULL
GO
select company, stepid, fieldid, foo from #Results
GO
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results
GO

方案二

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    Truncate TABLE #Results
else
    CREATE TABLE #Results
    (
        Company             CHAR(3),
        StepId              TINYINT,
        FieldId             TINYINT,
    )

方案三

if exists (
    select  * from tempdb.dbo.sysobjects o
    where o.xtype in ('U') 

   and o.id = object_id(N'tempdb..#tempTable')
)
DROP TABLE #tempTable;

方案四

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
    DROP TABLE #Results

CREATE TABLE #Results
(
    Company                CHAR(3),
    StepId                TINYINT,
    FieldId                TINYINT,
)

GO

select company, stepid, fieldid from #Results

IF OBJECT_ID('tempdb..#Results') IS NOT NULL
DROP TABLE #Results

CREATE TABLE #Results
(
    Company                CHAR(3),
    StepId                TINYINT,
    FieldId                TINYINT,
    NewColumn            NVARCHAR(50)
)

GO

select company, stepid, fieldid, NewColumn from #Results

阅读了该文章的人还浏览了...

本文永久链接码友网 » [SQL Server]SQL Server数据库中判断临时表是否存在并删除后创建的方法有哪些?

发布于: 2018-01-11 20:19:24
分享扩散:

文章评论

获取验证码