SQL Command query

Question

    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();

How do i tag with the database? Sorry i'm new to this, just started it today — John Doe
– John Doe, Commented Jul 7, 2017 at 11:08
try this: "SELECT * INTO BackUpDatabase.dbo.DriversDetailTable FROM Drivers.dbo.DriversDetailTable". Put dbo between database and table name. Or the schema your using. — bruno.almeida
– bruno.almeida, Commented Jul 7, 2017 at 11:12
You could also use INSERT INTO ... SELECT which seems to be more common in other databases. And you should specify column names if possible. — Tim Biegeleisen
– Tim Biegeleisen, Commented Jul 7, 2017 at 11:14

Pரதீப் · Accepted Answer · 2017-07-07 11:39:11Z

3

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

edited Jul 7, 2017 at 11:39

answered Jul 7, 2017 at 11:09

Pரதீப்

94.1k21 gold badges144 silver badges179 bronze badges

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

10 Comments

John Doe Over a year ago

System.Data.SqlClient.SqlException occurred HResult=0x80131904 Message=The specified schema name "BackUpDatabase" either does not exist or you do not have permission to use it. Source=.Net SqlClient Data Provider StackTrace: <Cannot evaluate the exception stack trace>

John Doe Over a year ago

do i have to delete the database table if it already exists because i'm getting this error

John Doe Over a year ago

System.Data.SqlClient.SqlException occurred HResult=0x80131904 Message=There is already an object named 'DriversDetailTable' in the database. Source=.Net SqlClient Data Provider StackTrace: <Cannot evaluate the exception stack trace>

Pரதீப் Over a year ago

@JohnDoe - Read my note at the end

John Doe Over a year ago

Not really sure how to identify the database schema

|

Collectives™ on Stack Overflow

SQL Command query

1 Answer 1

10 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

10 Comments

Your Answer

Sign up or log in

Post as a guest

Related