cant copy data from one DB to another.

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

cant copy data from one DB to another.

Author

Topic

Fatkitty
Starting Member

2 Posts

Posted - 2005-10-06 : 05:52:46

I am trying to copy item names from one database to another if they share the same code number.
e.g.

UPDATE TABLE1
SET NAME = DB2.TABLE2.NAME
WHERE CODE = DB2.TABLE2.CODE

I dont know how to specify the second db as using a '.' here doesnt seem to work. I have also tried '..' and DB2.dbo.TABLE2 but get the same error - 'The column prefix 'Train_DB_FinancialsProd.dbo.ITEM_DEFINITIONS' does not match with a table name or alias name used in the query.'

I have checked that the spelling is ok so not sure why it doesnt work.

I have also tried using a temp db in between to copy the values across then try to update from the temp table but couldnt make that work either. e.g.

create table #tdhname
(
code char(50),
name char(100)
)

INSERT INTO #tdhname (code,name)
select distinct code,name from Train_DB_FinancialsProd..item_definitions tritm
where code = tritm.code
and tritm.ittyp_refno = 241

update pharmacy_items
set name = #tdhname.name <----- THIS DOESNT WORK
where #tdhname.code = code

Thanks!

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-06 : 05:58:50

UPDATE TABLE1
SET NAME = b.Name
FROM Table1 a
INNER JOIN DB2.dbo.TABLE2 b
ON a.CODE = b.CODE

Duane.

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2005-10-06 : 06:02:49

Try this

update P
set name = T.name from pharmacy_items P inner join #tdhname.name T on P.code = T.code

Madhivanan

Failing to plan is Planning to fail

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2005-10-06 : 06:08:59