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. Thanks! "He laughs best who laughs last"
You can use a query to select news rows from primary server alongwith DTS to import/export rows. Satya SKJ Contributing Editor & Forums Moderator http://www.SQL-Server-Performance.Com This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
Satya...tha thing is iwant to insert new rows and also update old rows, I tried using DTS but no luck. Thanks! "He laughs best who laughs last"
Why not use temp table here and use adhoc query to pull required rows. Satya SKJ Contributing Editor & Forums Moderator http://www.SQL-Server-Performance.Com This posting is provided “AS IS†with no rights for the sake of knowledge sharing.
What references are stopping the inserts and updates? If you treate it as a linked server than you should be able to do an insert select where it does not exist, than an update select where it does exist.
Try this --To insert new Records Insert into DB1.dbo.Emp(Columns) Select Columns from DB2.dbo.Emp T2 where not exists (Select * from DB1.dbo.Emp T1 where T1.keycol=T2.keycol) --To update existing Records Update T1 set T1.col1=T2.col1, T1.col2=T2.col2,.... from DB1.dbo.Emp T1 inner join DB2.dbo.Emp T2 on T1.keycol=T2.keycol Madhivanan Failing to plan is Planning to fail
Do the update on matching rows BEFORE inserting new rows! If you do the insert first, the inserted rows will also be updated when you run the update second.
Thanks Adriaan. Thats Good Point [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
Adrian tht looks like gud point to me,I was doing like INSERT 1st then update and had no problem till now.But I have a question, I am inserting new rows if any exist in the source table, if I am updating it later still it updates the new rows what ever you have in the source table, which is what I need. Accroding to you where do u think it goes worng, if you dont can elaborate it with an example. Thanks! "He laughs best who laughs last"
Looks like it is the most common rule I need to follow. Anyways thanks Adrain n Madhivanan. Thanks! "He laughs best who laughs last"
>>Anyways thanks Adrain n Madhivanan.<br /><br />Well. But spell names correctly [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
OOOK. Thanks Adriaan and Madhivanan. Hey I doubt is it Madhivanan or Madhavan Thanks! "He laughs best who laughs last"
>>Hey I doubt is it Madhivanan or Madhavan<br /><br />No doubt. My name is Madhivanan [<img src='/community/emoticons/emotion-1.gif' alt='' />]<br />What is your full name?<br /><br />Madhivanan<br /><br />Failing to plan is Planning to fail
Hi.Reddy..if you have any interesting questions and answers please send it to me at vsnreddi@gmail.com Thanks..vsnreddy SURYA
