SQL guru required please!

glenimp617

glenimp617

glenimp617

Soldato
Joined
30 Sep 2005
Posts
16,623
Hi,

SQL Guru required please,

Basically I need the location field in DB2 to be the same as the portdescription field in DB1.

The data is all available to match the records, but I do not have any SQL experience to write the correct query. Hell, I don't
even know how to create the scheduled job lol

I was originally going to code it all in VBScript but when I looked at it, I think a SQL query would do it.

Help!!

Thanks!!

DB1 on SERVER1
--------------

dbo.UDT_Port - (portdescription, macaddress)

DB2 on SERVER2
--------------

dbo.networkinfo - (resourceid, macaddress)
dbo.resourcelocation - (resourceid, location)
 
yeah, a little more information may be needed to really help out.

If they aren't linked servers you should be able to use openrowset to use data between the 2 servers.

Is this a one off update or are you going to want the data to be automatically updated on a regular basis?


What I would probably do us use Replication to copy the DB1 dbo.UDT_Port - (portdescription, macaddress) table over to DB2 and it'll keep it updated as DB1 gets changed.

Then it is just a case of updating location on

This may be different dependant on the version of SQL being used:

BEGIN TRAN

UPDATE dbo.resourcelocation t1
SET t1.LOCATION = t2.portdescription
FROM dbo.UDT_Port t2
ON t1.macaddress = t2.macaddress

-- ROLLBACK
-- COMMIT
PRINT @@TRANCOUNT

You'd then put something like this in an SP (Stored Procedure) with a job to kick that off every X minutes
 
Brilliant, thanks guys

The data needs to be regularly updated, once a day will do

Remember the macaddress is in a different table in DB2 to where the location is set so was unsure how the matching would work
 
Without trying it(edit it didn't this does), this should work:

DO NOT DROP YOUR REAL TABLES AT THE END!

Code: 
CREATE TABLE #resourcelocation
(
    resourceid VARCHAR(50),
    LOCATION VARCHAR(50)
);


INSERT INTO #resourcelocation
 VALUES ('1',null),
('2',null),
('3',null) ;

CREATE TABLE #networkinfo
(
    resourceid VARCHAR(50),
    MACAddress VARCHAR(50)
);

insert into #networkinfo
values ('1','A'),
('2','B'),
('3','C')
;

CREATE TABLE #UDT_Port
(
    portdescription VARCHAR(50),
    MACAddress VARCHAR(50)
);

insert into #UDT_Port
values ('loc1','A'),
('loc2','B'),
('loc3','C')
;

select * from #resourcelocation;

UPDATE #resourcelocation
SET #resourcelocation.LOCATION = t2.portdescription
FROM
 #resourcelocation t1
 inner join #networkinfo n on n.resourceid = t1.resourceid
 inner join #UDT_Port t2 on t2.MACAddress = n.MACAddress;

 select * from #resourcelocation;

 drop table #resourcelocation;
 drop table #UDT_Port;
 drop table #networkinfo;
 
Last edited:
You must log in or register to reply here.
Back
Top Bottom