December 7, 2007 at 12:59 pm
I need to update a table (clients) in a SQL database (DTE2003) with data from another table in another SQL Database (CAPCMS). I have the query set, but I do not know how to make the cross-connection. Any help would be appreciated.
The preliminary query I have made is as follows:
-- Temp table from CAPCMS
select distinct inmatesinfo.lname + ', ' + inmatesinfo.fname + ' ' + inmatesinfo.mname As 'Defendant Name', Ct_No
#Temp
From inmatesinfo, casedetails
where inmatesinfo.inmateid = casedetails.inmateid
and casedetails.cap_active = 1
Union
select distinct inmatesinfo.lname + ', ' + inmatesinfo.fname + ' ' + inmatesinfo.mname As 'Defendant Name', Ct_No
From inmatesinfo, casedetails, casetocap_attymapping
where inmatesinfo.inmateid = casedetails.inmateid
and casedetails.newcaseid = casetocap_attymapping.newcaseid
and casetocap_attymapping.cap_id = 24 and relieved_date is null
-- Insert CAPCMS data into Clients table in DTE2003
Update Clients set appeal_no = #Temp.Ct_No where name = #temp.[defendant name]
December 14, 2007 at 10:48 am
Generally, the best way is going to be to create a linked server (Books Online has directions on how to do this), and then use the 4-part name of the table you want to insert into.
If, for example, the remote server is "Server2" and the database is "Database2", and the table is "Table2" (just to make this extremely generic), it would look something like:
insert into server2.database2.dbo.table2 (column1, column2)
select columnA, columnB
from dbo.table1
Does that help?
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
December 14, 2007 at 11:01 am
Thank you for responding to my question. Actually, after a few days of not hearing from anyone I didn't think anyone would respond, so I paid for assistance from Microsoft. The query that was developed worked very well, and is below. I'm not sure why aliases are needed in the last paragraph of the query, but the routine doesn't work without them.
Create Table #Temp
(
Defendant_Name Varchar(1000),
Ct_No varchar(100)
)
Insert Into #Temp
select distinct inmatesinfo.lname + ', ' + inmatesinfo.fname + ' ' + inmatesinfo.mname As 'Defendant Name', Ct_No
From inmatesinfo, casedetails where inmatesinfo.inmateid = casedetails.inmateid and casedetails.cap_active = 1
Union
select distinct inmatesinfo.lname + ', ' + inmatesinfo.fname + ' ' + inmatesinfo.mname As 'Defendant Name', Ct_No
From inmatesinfo, casedetails, casetocap_attymapping where inmatesinfo.inmateid = casedetails.inmateid
and casedetails.newcaseid = casetocap_attymapping.newcaseid
and casetocap_attymapping.cap_id = 24 and relieved_date is null
Update Clnts
Set appeal_no = Tmp.Ct_No
From DTE2003.DBO.Clients Clnts, #Temp Tmp
Where Clnts.name = Tmp.[defendant_name] and clnts.appeal_no = 'S000000'
Go
Drop Table #Temp
go
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply