November 7, 2005 at 8:52 am
Hi
I want to update a table Emp(DB1) from table Emp(DB2). I want to append new rows to it and also update any changes in the existing rows.How can I do this when table Emp(DB1) has relationships with another tables with in the DB1. I was trying to drop Emp, recreate Emp and fill Emp with new data but cudnt do it since it has references.
can any one give some idea how to accomplish this task.
November 7, 2005 at 8:59 am
To add new rows:
INSERT db1.dbo.Emp (col1, col2) SELECT col1, col2 FROM db2.dbo.Emp
To update existing rows:
USE db1
UPDATE a
SET a.col1 = b.col1
FROM db1.dbo.Emp a
JOIN db2.dbo.Emp b
ON a.primaryKey = b.primaryKey
Also, there is more info to read on SELECT and UPDATE in BOL (Books On Line)
/Kenneth
November 7, 2005 at 10:50 am
And in addition to what Kenneth said, if the second database "DB2" resides on a separate instance, you would need to use linked servers. Look up linked servers in BOL for more info..
November 7, 2005 at 4:53 pm
Say you have 2 tables in both databases:
Create table thing (
Id int IDENTITY(1,1),
Name nvarchar(50) NOT NULL,
typeId int NOT NULL)
Create Table ThingType (
Id int Identity (1,1),
Name nvarchar (50) NOT NULL )
INSERT INTO db1..Thing (Name, TypeId)
SELECT T2.Name, TP1.Id
FROM db2..Thing T2
INNER JOIN db2..ThingType TP2 on T2.TypeId = TP2.Id
INNER JOIN db1..ThingType TP1 on TP2.Name = TP1.Name
WHERE NOT EXISTS (select 1 from db1..Thing T1
where T1.Name = T2.Name and T1.TypeId = TP1.Id)
Use the same approach for UPDATE
_____________
Code for TallyGenerator
November 8, 2005 at 3:17 am
Actually, there's no need to check for existing rows on an update, since if rows do exist, they update, and if not, nothing happens. Updates doesn't risk causing pk violations like inserts do.
/Kenneth
Viewing 5 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply