Problem referencing a Table in another Database

Question

Post reply

Problem referencing a Table in another Database

theflyguy

SSC Rookie

Points: 31
More actions
June 5, 2008 at 5:35 am

#192008

What I'm trying to achieve is to complete some Records in one Database with corresponding records from another. Thought it could work like that:
use [MyDatabase]
go
declare @T sysname
set @T = 'second_db.dbo.more_records'
update adresslist set cust_street = @T.cust_street, cust_streetnr = @T.cust_streetnr, cust_town = @T.cust_town, cust_zip = @T.cust_zip, cust_addAddress = l@T.cust_addAddress
where (cust_street = '' or cust_street is null)
and cust_firstname = @T.cust_firstname
and cust_lastname = @T.cust_lastname
and order_nr = @T.order_nr
and offer_date = @T.offer_date
Obviously I'm doing something wrong while declaring the variable for the second Database, because the above statements gives me an Error:
"The @T -Table Variable must be declared"
Even something very easy like
use [myDB]
go
declare @T sysname
set @T = 'DB_test.dbo.Myrecords'
select * from @T
doesn't work. What am I doing wrong?

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

noeld SSC Guru Points: 96590 More actions · Answer 1

You can Use UPDATE FROM syntax

UPDATE dest SET col1 = src.col1 , col2 = src.col2 ...

FROM local_db_table dest join other_database.schema.othertable src

on dst.PK = src.PK

hth

* Noel

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 2

First, to do it the way you are trying, you need to use dynamic sql. I haven't written it that way yet, but based on your posted code I did write an update statement that should work without using a variable (ie hardcoded the the table from the other database).

use [MyDatabase]

go

update dbo.addresslist set

cust_street = mr.cust_street,

cust_streetnr = mr.cust_streetnr,

cust_town = mr.cust_town,

cust_zip = mr.cust_zip,

cust_addAddress = mr.cust_addAddress

from

dbo.addresslist al

inner join second_db.dbo.more_records mr

on (al.cust_firstname = mr.cust_firstname

and al.cust_lastname = mr.cust_lastname

and al.order_nr = mr.order_nr

and al.offer_date = mr.offer_date

where

(al.cust_street = ''

or al.cust_street is null)

😎

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 3

Here is the code using dynamic sql:

use [MyDatabase]

go

declare @T sysname,

@SQLCmd nvarchar(max)

set @T = 'second_db.dbo.more_records'

set @SQLCmd =

'update dbo.addresslist set