May 21, 2008 at 3:31 pm
I have 2 sql servers (TBServer1 and TBServer2) running and I need to update one database that is on TBServer2 with data on TBServer1
[StateLouisianaOld] is on TBServer1
[StateLouisianaNew] is on TBServer2
this does not work:
UPDATE [StateLouisianaNew].[TrailblzUser].[Person]
SET
[StateLouisianaNew].[TrailblzUser].[Person].[BirthDate] = [StateLouisianaOld].[TrailblzUser].[Person].[BirthDate]
WHERE
{
[StateLouisianaOld].[TrailblzUser].[Person].[Code] = [StateLouisianaNew].[TrailblzUser].[Person].[Code]
AND
[StateLouisianaOld].[TrailblzUser].[Person].[NotUniqueID] = [StateLouisianaNew].[TrailblzUser].[Person].[NotUniqueID]
}
I am sure I need to do something like have two connections open but can not find any help.
May 21, 2008 at 5:10 pm
bhanf (5/21/2008)
I have 2 sql servers (TBServer1 and TBServer2) running and I need to update one database that is on TBServer2 with data on TBServer1[StateLouisianaOld] is on TBServer1
[StateLouisianaNew] is on TBServer2
this does not work:
UPDATE [StateLouisianaNew].[TrailblzUser].[Person]
SET
[StateLouisianaNew].[TrailblzUser].[Person].[BirthDate] = [StateLouisianaOld].[TrailblzUser].[Person].[BirthDate]
WHERE
{
[StateLouisianaOld].[TrailblzUser].[Person].[Code] = [StateLouisianaNew].[TrailblzUser].[Person].[Code]
AND
[StateLouisianaOld].[TrailblzUser].[Person].[NotUniqueID] = [StateLouisianaNew].[TrailblzUser].[Person].[NotUniqueID]
}
I am sure I need to do something like have two connections open but can not find any help.
Did you create a linked server on either system? If not, you need to create a linked server so you can use four-part naming (e.g. server.database.schema.object).
With four-part naming, your query will then be:
UPDATE new
SET new.BirthDate = old.BirthDate
FROM StateLouisianaNew.TrailblzUser.dbo.Person new
INNER JOIN StateLouisianaOld.TrailblzUser.dbo.Person old ON old.PK = new.PK
WHERE new.Code = old.Code
AND new.NotUniqueID = old.NotUniqueID;
If you are going to doing a lot of queries against the old system, I would recommend creating a separate schema and Synonyms for the objects you are going to access. Create this on the new server, as well as create the linked server on the new server also.
CREATE SCHEMA OldSystem AUTHORIZATION dbo;
GO
CREATE SYNONYM OldSystem.Person FOR StateLouisianaOld.TrailblzUser.dbo.Person;
CREATE SYNONYM OldSystem.Address FOR StateLouisianaOld.TrailblzUser.dbo.Address;
...
Then, your update (run on StateLouisanaNew) would be:
UPDATE new
SET new.BirthDate = old.BirthDate
FROM dbo.Person new
INNER JOIN OldSystem.Person old ON old.PK = new.PK
WHERE new.Code = old.Code
AND new.NotUniqueID = old.NotUniqueID;
Note: you need to replace the PK with the actual primary key for each table and make sure the join does not return multiple rows.
HTH,
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 22, 2008 at 8:17 am
I don't have a primary key between systems, but the code and notuniqueid form a key.
Not sure how to create a linked server (so my answer is no i don't have one).
May 22, 2008 at 8:26 am
thanks for the information Jeff - still looking for how to link. found a stored proc but nothing on how to use it.
May 22, 2008 at 11:05 am
bhanf (5/22/2008)
I don't have a primary key between systems, but the code and notuniqueid form a key.Not sure how to create a linked server (so my answer is no i don't have one).
Does the combination of code and notuniqueid identify a single row? If so, then your update query will change to:
UPDATE new
SET new.BirthDate = old.BirthDate
FROM dbo.Person new
INNER JOIN OldSystem.Person old
ON old.Code = new.Code
AND old.NotUniqueID = new.NotUniqueID;
This will update every row in the Person table on the new server.
Jeff
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
May 22, 2008 at 11:11 am
thanks i will give this a try.
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply