CREATE TABLE IF NOT EXISTS equivalent in SQL Server [duplicate]

Question

CREATE TABLE IF NOT EXISTS works on mysql but fails with SQL Server 2008 R2. What is the equivalent syntax?

Actually this is not a duplicate of the marked question. This question is asking how to create if it does not exists. because we need a GO after create, we cannot put the create command inside BEGIN and END block, as the answer for other question suggests. — Bistro
– Bistro, Commented Oct 23, 2014 at 7:00
@JNK, when you where marking this question as basic, your link might have been on top of the search results. however on this date, this question is on top of Google search. — Bistro
– Bistro, Commented Oct 23, 2014 at 7:02
@Bistro it doesn't matter at all which question is higher ranked in google results. It matters which came first. This Q can still point to the original. — JNK
– JNK, Commented Oct 23, 2014 at 12:08
This was closed as a duplicate of a different question. The logic behind it being closed is like the XY problem in reverse. You’re telling the asker that they should have asked Y when they’re actually asking X (whereas in the XY problem the asker asks Y when they’re trying to solve X). I.e., the asker did the right thing and the question is closed? sad face — binki
– binki, Commented Jun 12, 2018 at 22:44

Neil Knight · Accepted Answer · 2012-12-11 16:21:57Z

282

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

The above will create a table called cars if the table does not already exist.

edited Dec 11, 2012 at 16:21

answered Jun 29, 2011 at 12:59

Neil Knight

48.8k26 gold badges136 silver badges193 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

marc_s Over a year ago

Better yet - use the specific sys.tables catalog view instead of having to remember what obscure xtype a table is...

Neil Knight Over a year ago

@marc_s: Yes. Another good way :o) In fact, probably a better way.

bart Over a year ago

Since this is the top question for this topic in Google even though it has been closed:

if not exists (select * from sys.tables t join sys.schemas s on (t.schema_id = s.schema_id) where s.name = 'myschema' and t.name = 'cars') 	create table myschema.cars (         Name varchar(64) not null     )

crucifery Over a year ago

Coming back to this in 2017: IF OBJECT_ID(N'dbo.Cars', N'U') IS NULL BEGIN CREATE TABLE dbo.Cars (Name varchar(64) not null); END;

Collectives™ on Stack Overflow

CREATE TABLE IF NOT EXISTS equivalent in SQL Server [duplicate]

1 Answer 1

4 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Linked

Related