CODEWITHSUNDEEP
c#sqlsql-server-2008
user2614235
user2614235

Reputation: 181

C# checking if a table exists in a database or not

I have two tables in the same database in SQL Server 2008. I want to copy contents of a table to another empty table having same schema, but when I execute the statement

OleDbDataAdapter da = new OleDbDataAdapter("select * into dbo.leadmastersnew from dbo.leadmasters", myConnection);

it throws an error saying the table already exists in the database.

I want that if the table already exists, the table should be deleted because the above SQL query creates the table and copies the contents to it.

Please suggest

My code is

private void button5_Click(object sender, EventArgs e)
{
     OleDbConnection myConnection = new OleDbConnection("File Name=E:\\Vivek\\ImplementUdl\\ImplementUdl\\new.udl");

     try
     {
         myConnection.Open();

         if (myConnection.State == ConnectionState.Open)
            MessageBox.Show("Connection opened successfully!");
         else
            MessageBox.Show("Connection could not be established");

         DataSet ds = new DataSet();

         OleDbDataAdapter da = new OleDbDataAdapter("select * into dbo.leadmastersnew from dbo.leadmasters", myConnection);

         da.Fill(ds);

         MessageBox.Show("Data Copied!!");
     }
     catch (Exception ex)
     {
         MessageBox.Show(ex.Message);
     }
     finally
     {
           myConnection.Close();
     }

     return;
}

Upvotes: 1

Views: 2476

Answers (4)

John Demetriou
John Demetriou

Reputation: 4371

A solution to your problem might be with this query

EXISTS (SELECT * 
                 FROM INFORMATION_SCHEMA.TABLES 
                 WHERE TABLE_SCHEMA = 'TheSchema' 
                 AND  TABLE_NAME = 'TheTable'))

After that, if it returns True then the table exists. Now you need to run an insertion query

String query = "INSERT INTO dbo.The_table(id,username,password,email) VALUES(@id,@username,@password, @email)";

SqlCommand command = new SqlCommand(query, db.Connection);
command.Parameters.Add("@id","abc");
command.Parameters.Add("@username","abc");
command.Parameters.Add("@password","abc");
command.Parameters.Add("@email","abc");

command.ExecuteNonQuery();

If the original statement returns false you need to run a create table query before runnig the insert query. The create table query looks a lot like this.

string queryString =  @"
CREATE TABLE MyTable
(
   Id  int IDENTITY(1,1) PRIMARY KEY CLUSTERED,
   Name        varchar(50) 
)";


    SqlCommand command = new SqlCommand(
        queryString, connection);

    command.ExecuteNonReader();

Upvotes: 1

Steve Pettifer
Steve Pettifer

Reputation: 2043

To answer the actual question of why the SQL won't work, the reason you get an error is because you have the following:

select * into dbo.leadmastersnew from dbo.leadmasters

SQL interprets that as a command to create the table leadmastersnew and then populates it. If you are set on using the solution you have then simply change your SQL to the following:

if object_id('dbo.leadmastersnew') is not null begin drop table dbo.leadmastersnew end select * into dbo.leadmastersnew from dbo.leadmasters

This will test to see if the table exists, drop it if so then create and populate the table again. It's not a terribly clean way to do things but if that's what you want then this will do it.

Upvotes: 1

Filip De Vos
Filip De Vos

Reputation: 11898

I would create an empty table if it doesn't exist yet and then copy in the data. If possible don't use udl files for connections, but store the connection string elsewhere

using(var conn = new SqlConnection("<connectionstring>"))
using(var cmd = new SqlCommand(@"
                if object_id('dbo.leadmastersnew') is null
                  begin
                      select * into dbo.leadmastersnew from dbo.leadmasters where 1=2
                      alter table dbo.leadmastersnew add primary key (<key>)
                  end
                insert into dbo.leadmastersnew
                select * 
                from dbo.leadmasters lm
                where not exists(select * from dbo.leadmastersnew lmn where lm.<key> = lmn.<key>)", conn))
{
    conn.Open();
    cmd.ExecuteNonQuery();
}

note that in the where clause you need to fill out the correct <key> columns. I also added a primary key constraint to the new table as select into will not copy the primary key/indexes of the source table.

Upvotes: 2

GarethD
GarethD

Reputation: 69749

I'd be inclined just to do it all in a single SQL Statement:

IF OBJECT_ID(N'dbo.leadmastersnew', 'U') IS NOT NULL
    DROP TABLE dbo.leadmastersnew;

SELECT  * 
INTO    dbo.leadmastersnew 
FROM    dbo.leadmasters;

Upvotes: 1

Related Questions