Create table if not exists

Transact-SQL
1

Hello SQL expert,

I need to create a table with the condition if the table already exists then the statement will not continue. How can I achieve that?

So far I have:

if (not exists (select object_id from sys.objects where object_id = OBJECT_ID(N'[dbo].[CustomersTbl]') and type = 'U')))
BEGIN
CREATE TABLE Customers
(
ID Varchar(20)
,Name (VARCHAR(100)
)
END;

The above statement continues executed after the table is created. How do I update it so the statement will not executed if the table already exists?

Thank you all

2 
IF 
 ( NOT EXISTS 
   (select object_id from sys.objects where object_id = OBJECT_ID(N'[dbo].[CustomersTbl]') and type = 'U')
 )
BEGIN
CREATE TABLE Customers
(
ID Varchar(20)
,Name (VARCHAR(100)
)
END;

image
image775×306 9.09 KB

3

You have to use the same table name in both places.

You are checking for the existence of table "CustomersTbl" but you are CREATEing table "Customers".

4

Instead of that you can use the shorter

IF OBJECT_ID(N'[dbo].[Customers]', 'U') IS NULL
BEGIN
1 Like