How to Simulate MySQL's CREATE TABLE IF NOT EXISTS in SQL Server?

SQL Server Equivalent for CREATE TABLE IF NOT EXISTS

In MySQL, the CREATE TABLE IF NOT EXISTS syntax creates a table only if it does not already exist. However, in SQL Server 2008 R2, this syntax is not supported.

Equivalent Syntax

To create a table with similar functionality in SQL Server, use the following syntax:

if not exists (select * from sysobjects where name='cars' and xtype='U')
    create table cars (
        Name varchar(64) not null
    )
go

Explanation

This query first checks if a table named 'cars' already exists in the database. If not, it proceeds to create the table as specified by the subsequent create table statement.

• if not exists checks if the table does not exist.
• sysobjects is a system table that stores information about all objects in the database, including tables.
• name='cars' and xtype='U' filters the sysobjects table to only check for table objects named 'cars'.

Example

The following example creates a table named 'customers' if it does not already exist:

if not exists (select * from sysobjects where name='customers' and xtype='U')
    create table customers (
        Customer_ID int not null primary key,
        Name varchar(64) not null
    )
go

The above is the detailed content of How to Simulate MySQL's CREATE TABLE IF NOT EXISTS in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!