CODEWITHSUNDEEP
c#sqlsql-server
John Doe
John Doe

Reputation: 1

SQL Command query

    System.Data.SqlClient.SqlException occurred
  HResult=0x80131904
  Message=Invalid object name 'Drivers.DriversDetailTable'.
  Source=.Net SqlClient Data Provider
  StackTrace:
<Cannot evaluate the exception stack trace>

Keep getting the above error everytime i try and copy stuff from one database table to another, heres my code.

SqlConnection connect = new SqlConnection($@"Data Source = Computer\SQLEXPRESS;Initial Catalog = Drivers;Trusted_Connection=True;");
            connect.Open();
            SqlCommand cmd = new SqlCommand($@"SELECT * INTO BackUpDatabase.DriversDetailTable FROM Drivers.DriversDetailTable", connect);
            cmd.ExecuteNonQuery();
            connect.Close();

Upvotes: 0

Views: 229

Answers (1)

Pரதீப்
Pரதீப்

Reputation: 93724

Looks like your database name is Drivers not schema name

SELECT * 
INTO BackUpDatabase.DriversDetailTable 
FROM Drivers.DriversDetailTable --here schema name is missing

When you use two part identifer then it will be parsed as

schemaname.tablename

So Drivers and BackUpDatabase is considered as schema names instead of database.

It should be

SELECT * 
INTO BackUpDatabase.dbo.DriversDetailTable 
FROM Drivers.schemaname.DriversDetailTable --here replace schemaname with dbo or whatever schema name

Note : When you use INTO clause it creates a new table. When you run second time you may get a error stating table already exist. Better to create table in prior in backup database use INSERT INTO..select to copy the rows

Insert into BackUpDatabase.dbo.DriversDetailTable (col1,col2,..)
select col1,col2,..FROM Drivers.schemaname.DriversDetailTable

Upvotes: 3

Related Questions