你真的很接近,但临时表也需要使用db名称。
IF OBJECT_ID('tempdb..#Results') IS NOT NULL DROP TABLE #Results GO
如果您正在检查另一个数据库中是否存在用户表,那将是相同的。
IF OBJECT_ID('myDatabase..myTable') IS NOT NULL DROP TABLE myDatabase..myTable GO
注意:有关BigDaddyO第一个建议的更多信息......
当您作为SQL批处理提交时,您提供的代码段将被解析为单个工作单元 的 先 强> 执行。最终结果是,如果 #temp_table 批处理提交时已存在,然后编译 create table 命令将生成错误。在以下示例中可以看到此行为:
#temp_table
create table
create table #mytab (a int, b varchar(30), c datetime) go -- your code snippet; during compilation the 'create table' generates the error -- because ... at the time of compilation #mytab already exists: if object_id('#mytab') is not NULL drop table #mytab create table #mytab (a int, b varchar(30), c datetime) go Msg 12822, Level 16, State 1: Server 'ASE200', Line 3: Cannot create temporary table '#mytab'. Prefix name '#mytab' is already in use by another temporary table '#mytab'. -- same issue occurs if we pull the 'create table' into its own batch: create table #mytab (a int, b varchar(30), c datetime) go Msg 12822, Level 16, State 1: Server 'ASE200', Line 1: Cannot create temporary table '#mytab'. Prefix name '#mytab' is already in use by another temporary table '#mytab'.
正如BigDaddyO建议的那样,解决这个问题的一种方法是将代码段分成两个单独的批处理,例如:
-- test/drop the table in one batch: if object_id('#mytab') is not NULL drop table #mytab go -- create the table in a new batch; during compilation we don't get an error -- because #mytab does not exist at this point: create table #mytab (a int, b varchar(30), c datetime) go
可能不是一个很好的回应,但我也有这个问题,我有两种方法。 1.将IF OBJECT_ID Drop Table作为查询之前的单独执行 2.在查询后立即执行没有IF OBJECT_ID()的Drop Table。