如果表不存在，如何仅创建表?使用“object_id('table') IS NULL"不起作用? [英] How to only create table if it doesn't exist? Using "object_id('table') IS NULL" doesn't work?

问题描述

我想检查该表是否不存在，然后创建一个，否则，向其中插入数据.

I would like to check if the table does not exist, then create one, otherwise, insert data into it.

use tempdb
if object_id('guest.my_tmpTable') IS NULL 
begin
   CREATE TABLE guest.my_tmpTable (
    id int,
    col1 varchar(100)
   )
end 

--- do insert here ...

第一次运行时，表已创建，但第二次运行时 sybase 抱怨该表已存在.

The first time run, the table was created but the 2nd run the sybase complains the table already exist.

谢谢！

推荐答案

原因是整个语句 - if 和它的true"部分被编译为一个.

The reason is that the entire statement - if and its "true" part are compiled as one.

如果在编译期间表存在 - 错误.

If the table exists during compilation - error.

所以你可以将 CREATE TABLE 语句放入动态 sql 语句中 EXEC('CREATE TABLE....')

So you could put the CREATE TABLE statement into a dynamic sql statemetn EXEC('CREATE TABLE....')

那么 Sybase 在编译时看到的是:

Then all that Sybase sees at compilation is:

IF object_id('mytab') IS NULL
  EXEC('something or other')

EXEC 的内容直到执行时才被编译，当你知道没有表并且一切正常时.

The contents of EXEC are not compiled until execution, by when you know there's no table and all is well.

这篇关于如果表不存在，如何仅创建表?使用“object_id('table') IS NULL"不起作用?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！