检查表是否存在 SQL

Question

我有一个查询如下：

    INSERT INTO CarnetMaster.GlassLookupCapacitySpecs
(ID, CODE, NVIC, RELEASE, DISCON, DRV, TORQUE, KW, BORESTROKE, VINNUMBER, WIDTH, WHEELBASE, SEATS, COMPRATIO, TOWCAP, STEER,
TURNCIR, HEIGHT, LENGTH, VWIDTH, KERBWT, PAYLOAD, GCM, GVM)

SELECT NEWID(), CODE, NVIC, RELEASE, DISCON, DRV, TORQUE, KW, [BORE-STROKE], [VIN NUMBER], WIDTH, WHEELBASE, SEA, [TS COMPRAT], [IO TOWC],
[AP STE], [ER TURNC], [IR HEIG], [HT LENG], [TH VWID], [TH KERB], [WT PAYLO], [AD GCM], GV
FROM CVG86_SPE

如何检查表是否存在，然后从表CVG86_SPE中选择并插入？如果表不存在，那么它不应该执行任何操作。

Answer 1

你可以这样做：
SQL 2000 语法

    IF EXISTS (SELECT 1 FROM sysobjects  WHERE xtype='u' AND name='CVG86_SPE') 
BEGIN
             INSERT INTO CarnetMaster.GlassLookupCapacitySpecs
    (ID, CODE, NVIC, RELEASE, DISCON, DRV, TORQUE, KW, BORESTROKE, VINNUMBER, WIDTH, WHEELBASE, SEATS, COMPRATIO, TOWCAP, STEER,
    TURNCIR, HEIGHT, LENGTH, VWIDTH, KERBWT, PAYLOAD, GCM, GVM)

    SELECT NEWID(), CODE, NVIC, RELEASE, DISCON, DRV, TORQUE, KW, [BORE-STROKE], [VIN NUMBER], WIDTH, WHEELBASE, SEA, [TS COMPRAT], [IO TOWC],
    [AP STE], [ER TURNC], [IR HEIG], [HT LENG], [TH VWID], [TH KERB], [WT PAYLO], [AD GCM], GV
    FROM CVG86_SPE 
END

SQL 2005 及更高版本：

        IF EXISTS ( SELECT TABLE_NAME FROM INFORMATION_SCHEMA.TABLES
WHERE TABLE_NAME = 'CVG86_SPE' )
BEGIN
INSERT INTO CarnetMaster.GlassLookupCapacitySpecs
        (ID, CODE, NVIC, RELEASE, DISCON, DRV, TORQUE, KW, BORESTROKE, VINNUMBER, WIDTH, WHEELBASE, SEATS, COMPRATIO, TOWCAP, STEER,
        TURNCIR, HEIGHT, LENGTH, VWIDTH, KERBWT, PAYLOAD, GCM, GVM)

        SELECT NEWID(), CODE, NVIC, RELEASE, DISCON, DRV, TORQUE, KW, [BORE-STROKE], [VIN NUMBER], WIDTH, WHEELBASE, SEA, [TS COMPRAT], [IO TOWC],
        [AP STE], [ER TURNC], [IR HEIG], [HT LENG], [TH VWID], [TH KERB], [WT PAYLO], [AD GCM], GV
        FROM CVG86_SPE
END

Answer 2

在 SQL Server 中执行相同操作的另一种方法是使用 OBJECT_ID() 函数：

IF OBJECT_ID('table name', 'U') IS NOT NULL BEGIN
  INSERT ...
  ...
END

第二个参数告诉函数专门搜索表，而不是搜索任意对象，例如存储过程或用户定义的函数。

如果要检查临时表是否存在，请像这样使用它：（

IF OBJECT_ID('tempdb..#tmp table name') IS NOT NULL BEGIN
  INSERT ...
  ...
END

这里第二个参数是不必要的，因为从对象的名称可以清楚地看出它只能是一个表。）