Web Design that taps into the haromny and vision of your dreams.

Create Table in SQL Server IF it does NOT Exist

Create table if not exists in MS SQL Server

Written By on in SQL

158 words, estimated reading time 1 minutes.

There are many reasons to create a table using a script, and a quick check if the table already exists will eliminate problematic errors, however, Microsoft SQL Server 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 prior to 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 exist, 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, but it functions in the same way.

Last updated on: Tuesday 20th June 2017

 

Comments

There are no comments for this post. Be the first!

 

Leave a Reply

Your email address will not be published.





If you find something abusive or that does not comply with our terms or guidelines please flag it as inappropriate.

 

Copyright © 2001-2018 Tim Trott, all rights reserved. Web Design by Azulia Designs

This web page is licensed for your personal, private, non-commercial use only.

Disclaimer, Privacy & LegalSitemapContact Me