2

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();
Improve this question
1
  • What error do you get? Does it not show up? Commented May 27, 2010 at 17:48

6 Answers 6

Reset to default
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.

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

3 Comments

Still doesn't work.. Once it is created, I run the app again and it fails stating 'table Import already exists.
What do you see if you run SELECT * FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME = 'Import'? (See Andomar's answer)
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.
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?

Improve this answer

Comments

5

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

Improve this answer

1 Comment

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.
2

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

Specify the full name, like:

CREATE TABLE RX_CMMData.Import
Improve this answer

Comments

0

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

Improve this answer

3 Comments

Oracle allows CREATE OR REPLACE
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.
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)
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

Improve this answer

Comments

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.