C# SQL create table IF it doesn't already exist

Question

Hey, I am trying to put a little logic into my C# app that will create a table called Import, IF it doesn't already exist.. here is my code, it doesn't seem to work tho.

con.Open();
                    using (SqlCommand cmd = con.CreateCommand())
                    {
                        cmd.CommandText =
                            @" 
                       IF (EXISTS (SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_SCHEMA = 'RX_CMMData' AND TABLE_NAME = 'Import'))
BEGIN
CREATE TABLE Import (
  RowId     integer PRIMARY KEY NOT NULL,
  PartNumber  varchar(200) NOT NULL,
  CMMNumber   varchar(200) NOT NULL,
  Date        varchar(200) NOT NULL,
  FeatType    varchar(200) NOT NULL,
  FeatName    varchar(200) NOT NULL,
  Value       varchar(200) NOT NULL,
  Actual      decimal,
  Nominal     decimal,
  Dev         decimal,
  TolMin      decimal,
  TolPlus     decimal,
  OutOfTol    decimal,
  FileName    varchar(200) NOT NULL
); END";
                        cmd.ExecuteNonQuery();
                    }
                    con.Close();

What error do you get? Does it not show up? – Tony May 27 '10 at 17:48 — Tony, May 27 '10 at 17:48

score 9 · Answer 1 · edited Apr 27 '12 at 07:23

9

Your SQL is creating the table if it does exist and not if it doesn't.

Change the SQL to read IF NOT EXISTS.

edited Apr 27 '12 at 07:23

bluish

26,356
27
122
180

answered May 27 '10 at 17:46

Marc Tidd

1,066
9
11

Still doesn't work.. Once it is created, I run the app again and it fails stating 'table Import already exists. – Jake Sankey May 27 '10 at 18:03
What do you see if you run `SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Import'`? (See Andomar's answer) – Martin Smith May 27 '10 at 18:10
1

Yes, you are querying INFORMATION SCHEMA looking for TABLE_SCHEMA = 'RX_CMMData' but your create statement is creating the table in the default schema which is probably dbo. See syntax in Andomar's answer. – Marc Tidd May 27 '10 at 18:19

score 5 · Accepted Answer · edited Apr 27 '12 at 07:23

5

The syntax of that under SQL Server 2005 is

IF NOT EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[tablename]') AND type in (N'U'))
BEGIN
  -- create
END

In your code, where is the = FALSE or NOT keyword?

edited Apr 27 '12 at 07:23

bluish

26,356
27
122
180

answered May 27 '10 at 17:46

Fredou

19,848
10
58
113

score 5 · Answer 3 · answered May 27 '10 at 18:22

You can also look into using Microsoft SMO objects. I prefer to work SMO objects and let them do the work for me if possible instead of executing SQL text through code.

Using the SMO Database object you can do the following.

Database db = myServer.Databases["MyDB"];

if (! db.Tables.Contains("NewTable"))
{

    Table tbl = new Table(db, "NewTable");

    Column col1 = new Column(tbl, "Column1", DataType.Varchar(10));
    col1.Nullable = true;
    tbl.Columns.Add(col1);

    tbl.Create();

}

http://www.mssqltips.com/tip.asp?tip=1826

Different strokes and all that but I find it much easier to just write the SQL Script than need to create all the objects and set the properties as in your example code. — Martin Smith, May 27 '10 at 18:32

score 2 · Answer 4 · answered May 27 '10 at 18:07

2

You're probably creating the table in the default schema (typically dbo.)

Specify the full name, like:

CREATE TABLE RX_CMMData.Import

answered May 27 '10 at 18:07

Andomar

232,371
49
380
404

score 0 · Answer 5 · answered May 27 '10 at 17:43

0

Some DBMSes allow you to do CREATE TABLE IF NOT EXISTS <tablename> inside the SQL. I'd check the syntax for your DBMS.

answered May 27 '10 at 17:43

J. Polfer

12,251
10
54
83

1

Oracle allows CREATE OR REPLACE – Paul Michaels May 27 '10 at 17:44
If you do a CREATE OR REPLACE in Oracle, will that delete the data that is currently stored in the table, or only alter the table definition? It doesn't feel like quite the same thing. – J. Polfer May 27 '10 at 17:50
No - if the table exists then the data will be fine (providing you don't make any changes that would affect the data, like removing a column or something) – Paul Michaels May 27 '10 at 18:05

score 0 · Answer 6 · answered May 27 '10 at 17:47

0

If you're using Oracle, you could try

select count(*) from user_tables
where table_name = ''

which should tell you if the table exists

answered May 27 '10 at 17:47

Paul Michaels

16,185
43
146
269

C# SQL create table IF it doesn't already exist

6 Answers6