CREATE TABLE IF NOT EXISTS equivalent in SQL ServerHow to use CREATE TABLE IF NOT EXISTS in SQL Server which does not provide a complete user-friendly way for testing table existence.
MySql contains a very useful table construct element which checks that the table does not exist before creating it. MySql's create table if not exist
construct will do as it says, it will create the table if it does not exist.
CREATE [TEMPORARY] TABLE [IF NOT EXISTS] tbl_name
(create_definition,...)
[table_options]
[partition_options]
Microsoft SQL Server lacks the function of create table if not exists
, meaning table creation queries will fail if the table already exists. You could drop the table before creating it, but again, you may run into problems if the table does not exist.
This function can be used to test if the table exists and, if it does not exist, create it.
IF NOT EXISTS (SELECT * FROM sysobjects WHERE name='tbl_name' and xtype='U')
CREATE TABLE tbl_name (
Name varchar(64) not null
)
GO
It is nowhere near as elegant as the MySql version CREATE TABLE IF NOT EXISTS
, but it functions in the same way.