Transferring information between one database and another
-
Hi.
SQL Server2000.
Can someone point me in the correct direction please.
I have two databases testDB and liveDB.
I need to update certain records in a table in one db with values from a table in the other db.
e.g. testDB has table tbl1Student and liveDB has table tblStudent
both tables contain the following structure: -
studentID int
rollStatus char(1)
Form int
Surname varchar(50)
Forename varchar(50)
I need to write a stored procedure that will first update the rollStatus, and Form of any records in the tblstudent of the liveDB where the RecordID matches one in the tbl1Student of the testDB (using values from the testDB).
Once done, I need to then add all new records from the tbl1Student testDB to the tblStudent liveDB. A new record is determined where the RecordID exists in the tbl1Student testDB but does not exist in the tblstudent liveDB.
Sorry if this is confusing.
Any suggestions?
-
I guess that studentID should be recordID in your table(s)
I also assume the databases are on the same server.I haven't tested anything, but maybe something like the following will work:
update t
set
t.rollStatus = t1.rollStatus,
t.Form = t1.Form
from
liveDB..tblstudent t, testDB..tbl1student t1
where
t.recordID = t1.recordID
insert into testDB..tbl1student
select * from liveDB..tblstudent
where recordID not in (select recordID from testDB..tbl1student)
-
Few observations, good practice
use ansi joins
update l
set l.rollStatus = t.rollStatus, l.Form = t.Form
from liveDB..tblstudent l
inner join testDB..tbl1student t on t.studentID = l.studentID
always declare column names instead of * where possible
use not exists instead of in (...) it will be faster on large tables
insert into liveDB..tblstudent
(studentID,rollStatus,Form,Surname,Forename)
select t.studentID,t.rollStatus,t.Form,t.Surname,t.Forename
from testDB..tbl1student t
where not exists (select l.recordID from liveDB..tblstudent l where l.recordID = t.recordID)
p.s. (Added)
wrap all updates/inserts in TRANSACTION so that you can check the results before COMMIT
Far away is close at hand in the images of elsewhere.
Anon. -
Thanks, David, not the first time I have learned something from you
-
Thank you Jesper, just reciprocating the favour
Far away is close at hand in the images of elsewhere.
Anon. -
Many Thanks guys.
CCB
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply