upadate database1.table1 wirh values from database2.table1

rgb192
rgb192 used Ask the Experts™
on
upadate database1.table1 wirh values from database2.table1
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
Ephraim WangoyaSoftware Engineer

Commented:

Create a linked database then you can use this format

insert into database1.dbo.table1
select * from database2.dbo.table2

Author

Commented:
Invalid object name database1.dbo.table1.    Where table1 is the new table on the first database
Ephraim WangoyaSoftware Engineer

Commented:
sorry

You have to use the server name

insert into [server1].[database1].[dbo].[table1]
select * from table2
Acronis in Gartner 2019 MQ for datacenter backup

It is an honor to be featured in Gartner 2019 Magic Quadrant for Datacenter Backup and Recovery Solutions. Gartner’s MQ sets a high standard and earning a place on their grid is a great affirmation that Acronis is delivering on our mission to protect all data, apps, and systems.

Author

Commented:
use database2
insert into server.database.dbo.orderitems12
select * from orderitems

Msg 8101, Level 16, State 1, Line 1
An explicit value for the identity column in table 'server.database.dbo.orderitems12' can only be specified when a column list is used and IDENTITY_INSERT is ON.


table orderitems123 has not be created

use database2
insert into purchase.ow1222.dbo.orderitems123
select * from orderitems
Invalid object name 'database.dbo.orderitems123'.
Software Engineer
Commented:
You have two options to insert into an identity column

1. Specifically omit the identity column from the insert list
insert into server.database.dbo.orderitems12(col1, col2, col3)
select col1, col2, col3 from orderitems

2. Set identity on (I'm not sure if you can do this on a linked server, I've never tried it)
SET IDENTITY_INSERT server.database.dbo.orderitems12 ON
insert into server.database.dbo.orderitems12
select from orderitems
SET IDENTITY_INSERT server.database.dbo.orderitems12 OFF
Top Expert 2012
Commented:
>>Set identity on (I'm not sure if you can do this on a linked server, I've never tried it)<<
I am afraid not.

Some questions:
1. Are these two databases on different servers?  I did not read that in your original question.  Did I miss it somewhere else?
2. Are you trying to UPDATE (as in your original question) or INSERT (as in subsequent comments)?
3. If you are trying to UPDATE what is the relationship between the two tables?

Author

Commented:
thanks

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial