0

I have two different databases. I have tried using an UPDATE query, but it comes back with an unrecognised error .

How can I reference the DB in the SQL query.

The location is like this for both:

SERVER01\ABC.DB1

And

SERVER01.DB2

EDIT 1:

I have tried 

insert into [DB1].[dbo].[table1]
select col1 from [ABC.DB2].[dbo].[table2]

But, I get this error,

Invalid object name 'ABC.DB2.dbo.table2'.
AndroidAL
  • 1,111
  • 4
  • 15
  • 35

1 Answers1

0

You can specify the database name in the query:

insert into [DbName1].[dbo].[Table1]
select * from [ABC.DB2].[dbo].[Table1]
trailmax
  • 34,305
  • 22
  • 140
  • 234
  • why would I be inserting and selecting from `DbName1`? – AndroidAL Dec 03 '15 at 10:15
  • @AndroidAL yes, sorry, It should be `DbName2` - updated – trailmax Dec 03 '15 at 10:17
  • it returns this error `Invalid object name 'server01\ABC.DB2.dbo.table1'.` I can update question to show what I have tried? – AndroidAL Dec 03 '15 at 10:26
  • @AndroidAL you don't need to include server name in the request - both databases are on the same server. Also try enclosing database name, schema name and table in square brackets `[]`. Added another update to my answer. And you should be able to edit your question. – trailmax Dec 03 '15 at 10:31
  • @AndroidAL in this case are you sure that all the names (including schema name) are correct? – trailmax Dec 03 '15 at 10:49
  • yes , I have checked them 3 times, is there another way that this could be done? – AndroidAL Dec 03 '15 at 10:55
  • Well, you can Generate scripts with data from one DB and execute them on on the other table, but this is impractical if you have a large dataset. – trailmax Dec 03 '15 at 11:02
  • Could you give me an example of a script or were I might find one? – AndroidAL Dec 03 '15 at 11:25
  • @AndroidAL Here: http://stackoverflow.com/a/2321082/809357 or here: http://blog.sqlauthority.com/2012/07/18/sql-server-generate-script-for-schema-and-data-sql-in-sixty-seconds-021-video/ – trailmax Dec 03 '15 at 11:27