0

I am trying to copy records from one database to another on the same server. It keeps giving me an Invalid Object Name error for the database I am copying from, but the name is correct. Target database is BS_Database and the Source Database is BS_Copy. Here is my query:

    Insert into BS_Database.tbl_Training_Attempt_New
([AttemptId]
      ,[RequirementId]
      ,[EmployeeCompanyId]
      ,[TaskId]
      ,[CourseId]
      ,[RecordId]
      ,[StartDate]
      ,[Score]
      ,[IsComplete]
      ,[CompleteDate]
      ,[IsPassed]
      ,[Active]
      ,[TimeInSession])
      SELECT
      [AttemptId]
      ,[RequirementId]
      ,[EmployeeCompanyId]
      ,[TaskId]
      ,[CourseId]
      ,[RecordId]
      ,[StartDate]
      ,[Score]
      ,[IsComplete]
      ,[CompleteDate]
      ,[IsPassed]
      ,[Active]
      ,[TimeInSession]
      FROM BS_Copy.tbl_Training_Attempts_New WHERE
      IsPassed = 0
Thom A
  • 88,727
  • 11
  • 45
  • 75
Rani Radcliff
  • 4,856
  • 5
  • 33
  • 60
  • You missed schema part - you at least need `Insert into BS_Database..tbl_Training_Attempt_New ... FROM BS_Copy..tbl_Training_Attempts_New`, if all your tables reside in dbo schema. – Arvo Jun 29 '22 at 11:47
  • That query only uses 2 part naming; it isn't cross database. – Thom A Jun 29 '22 at 11:47
  • So I need more than just the name of the database, and the table? I need to add the "dbo" in front of each table name? – Rani Radcliff Jun 29 '22 at 11:49
  • BS_Copy.dbo.[tbl_Training_Attempts_New]. This does not work either. – Rani Radcliff Jun 29 '22 at 11:51
  • You should **always** be defining the schema when referencing an objects, @RaniRadcliff . In the above `BS_Database.tbl_Training_Attempt_New` references the object `tbl_Training_Attempt_New` on the **schema** `BS_Database` in the database you are currently connected to. To reference an object in a different database, then the format needs to be `DatabaseName.SchemaName.ObjectName`. – Thom A Jun 29 '22 at 11:52
  • you need that also for BS_Database.dbo.tbl_Training_Attempt_New. Every table (view, stored proc,..) needs db and schema and table name if you are quering cross database – GuidoG Jun 29 '22 at 11:53
  • Not necessary,. @GuidoG. *Presumably* the OP is connected to either the destination or source database; we just don't know which. They only need 3 part naming for the object that isn't in the database that they are connected to. In the above, only one of the objects should require 3 part naming (`DatabaseName.SchemaName.ObjectName`), while the should have require 2 part naming (`SchemaName.ObjectName`). – Thom A Jun 29 '22 at 11:54
  • DatabaseName = BS_Copy, SchemName would be "dbo"? and Table name tbl_Training_Attempt_new, right? – Rani Radcliff Jun 29 '22 at 11:55
  • @Larnu Yes you are right, I myself do it for all objects in every database when quering cross database, but for the connected database it is indeed not needed. I stand corrected – GuidoG Jun 29 '22 at 11:56
  • *Assuming* you are using the default schema, then yes it would be `dbo`, @RaniRadcliff . If you aren't using the default schema, we have no idea what the schema name would be, – Thom A Jun 29 '22 at 11:56
  • When I add the dbo, I get Reference to database and/or server name in 'BS_Database.dbo.tbl_Training_Attempt_New' is not supported in this version of SQL Server. – Rani Radcliff Jun 29 '22 at 11:58
  • 1
    Are you using Azure SQL database by chance? If yes, then https://azure.microsoft.com/en-us/blog/querying-remote-databases-in-azure-sql-db/ – Arvo Jun 29 '22 at 12:02
  • @Arvo - yes it is an Azure database. I am in SSMS. I didn't think the the query would have to be written differently. I will check out the blog. Thanks! – Rani Radcliff Jun 29 '22 at 12:08
  • 1
    This is starting to smell of an [XY Problem](//xyproblem.info) now. Azure SQL Databases are contained databases by design, you can't do cross database queries and normally the "requirement" to means a different problem. – Thom A Jun 29 '22 at 12:17

1 Answers1

0

if you follow this way every thing will gong to be transferred

insert into [Destination db].[schema].[table name](Column1,column2,...)
select * from [Source db].[schema].[table name](Column1,column2,.....) where column1=1 and ......

in your query you missed schema if you follow default schema should be specify dbo

Upender Reddy
  • 568
  • 3
  • 8