Query to update rows and then create a table if it does not exist

Question

I have two different things to happen at once.., basically I need to insert two new records in a table if they are not already there... and for sure they will always have the same ID and Name (i did this) but then immediately after that i need to check if a table existst and if it does not create it.. (but if it does exist I DO NOT want to drop it just leave it alone).

Please see my code below.. Can you help me with the checking of the table existing or not? and if you see a room on improvement please do..

Thank you

--ADD LOCKS
BEGIN TRAN
IF EXISTS (SELECT myID, myName 
           FROM myTable
           WHERE myID = 7 AND myName = 'Pedro') 
               SELECT 1 
ELSE
      INSERT INTO myTable (myID , myName) values ( 7, 'Pedro')    

IF EXISTS (SELECT myID, myName 
           FROM myTable
           WHERE myID = 8 AND myName = 'Joseph') 
               SELECT 1 
ELSE
      INSERT INTO myTable (myID , myName) values ( 8, 'Joseph')    
COMMIT

--NOW BELOW I WANT TO DO THE CREATION OF A TABLE IF IT DOES NOT EXIST

NOT SURE HOW TO CHECK IT.. BUT KNOW HOW TO CREATE IT

--IF TABLE DOES NOT EXIST DO THE FOLLOWING
SET ANSI_NULLS ON
GO

SET QUOTED_IDENTIFIER ON
GO

CREATE TABLE [dbo].[myTable](
    [myID] [int] IDENTITY(1,1) NOT NULL,
    [Name] [varchar(MAX)] NOT NULL
 CONSTRAINT [PK_myTable] PRIMARY KEY CLUSTERED 
(
    [myID] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

--ELSE DONT DO NOTHING

Praveen · Accepted Answer · 2011-09-29T15:04:02.623

As per my knowledge, you have check whether the table exists or not first and create it before inserting if it doesn't exist. Hope the below query might be of some use for you.

     IF NOT EXISTS (SELECT * FROM SYSOBJECTS WHERE ID = OBJECT_ID(N'[dbo].[myTable]') AND OBJECTPROPERTY(id, N'IsUserTable') = 1)
     BEGIN
CREATE TABLE [dbo].[myTable]
(     
    [myID] [int] IDENTITY(1,1) NOT NULL,     
    [Name] [varchar(MAX)] NOT NULL  
    CONSTRAINT [PK_myTable] PRIMARY KEY CLUSTERED  
    (     [myID] ASC 
    )WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON
) ON [PRIMARY] ) ON [PRIMARY] 
    END

    IF NOT EXISTS (SELECT myID, myName  FROM myTable WHERE myID = 7 AND myName = 'Pedro')  
    BEGIN  
           INSERT INTO myTable (myID , myName) values ( 7, 'Pedro')    
   END  

   IF NOT EXISTS (SELECT myID, myName  FROM myTable WHERE myID = 8 AND myName = 'Joseph')  
    BEGIN  
        INSERT INTO myTable (myID , myName) values ( 8, 'Joseph')    
    END

Edit: Should also note, this will not work without turning IDENTITY_INSERT ON. Because the myID column is an identity field, the values attempting to be inserted will fail.

score 0 · Answer 2 · edited May 23 '17 at 10:34

For inserting the records cleaner script would be:

IF NOT EXISTS (SELECT 1 FROM myTable WHERE ...)
BEGIN
    -- Insert record
END

And for checking if a table exists: Check if table exists in SQL Server

IF (NOT EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'TheSchema' AND  TABLE_NAME = 'TheTable'))
BEGIN
    -- Insert table
END

You might want to switch the order of these statements so you don't query a table that may not exist.

Query to update rows and then create a table if it does not exist

2 Answers2