CODEWITHSUNDEEP
sql-servert-sqlsql-server-2014
MCP_infiltrator
MCP_infiltrator

Reputation: 4179

SQL Server stored procedure INSERT INTO

I have a stored procedure that should insert data into a table should that table exist. That portion of the code is in the ELSE BEGIN ... END statement. I get an error that the OBJECT already exists...

My code is like the following:

IF NOT EXISTS(SELECT TOP 1 * FROM SYSOBJECTS 
              WHERE name = 'smsdss.c_order_utilization_lihn_svc_w_order_dept_desc_bench' 
                AND xtype = 'U')
BEGIN
    CREATE TABLE SCHEMA.MY_TABLE ()
END
ELSE BEGIN
    DECLARE @TODAY_B DATE;
    DECLARE @START_B DATE;
    DECLARE @END_B   DATE;

    SET @TODAY_B = GETDATE();
    SET @START_B = DATEADD(YEAR, DATEDIFF(YEAR, 0, @TODAY_B) -1, 0);
    BLAH BLAH BLAH

    INSERT INTO SCHEMA.MY_TABLE
        SELECT 
            COLUMN_A,
            COLUMN_B, 
            ..... ,
            COLUMN_N
        FROM 
            #TEMP_B
        WHERE 
            ORDER_YEAR > (SELECT MAX(ZZZ.ORDER_YEAR) FROM SCHEMA.MY_TABLE)
END

Upvotes: 0

Views: 62

Answers (2)

Ilyes
Ilyes

Reputation: 14928

You can also use OBJECT_ID() as

IF OBJECT_ID('SCHEMA.MY_TABLE') IS NULL
BEGIN
  CREATE TABLE ...
END
  ELSE
    BEGIN
      INSERT ..
    END

Upvotes: 0

Igor
Igor

Reputation: 62213

The error indicates that the code is failing on the CREATE TABLE script inside the IF block, not the ELSE. Likely your SELECT statement is incorrect.

You can fix that or try this instead and double check the schema and table name.

IF NOT EXISTS (SELECT 1 FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'smsdss' AND TABLE_NAME = 'c_order_utilization_lihn_svc_w_order_dept_desc_bench')
BEGIN
....

Upvotes: 1

Related Questions