SQL Server Performance

Update

Discussion in 'General Developer Questions' started by Reddy, Nov 7, 2005.

  1. Reddy New Member

    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"

  2. satya Moderator

    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.
  3. Reddy New Member

    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"

  4. satya Moderator

    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.
  5. DStevensTN New Member

    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.
  6. Madhivanan Moderator

    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
  7. Adriaan New Member

    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.
  8. Madhivanan Moderator

    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
  9. Reddy New Member

    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"

  10. Adriaan New Member

    Yes, that's exactly what I was talking about.
  11. Madhivanan Moderator

    Follow this

    1 Updation
    2 Insert new records

    Madhivanan

    Failing to plan is Planning to fail
  12. Reddy New Member

    Looks like it is the most common rule I need to follow. Anyways thanks Adrain n Madhivanan.

    Thanks!
    "He laughs best who laughs last"

  13. Madhivanan Moderator

    &gt;&gt;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
  14. Reddy New Member

    OOOK. Thanks Adriaan and Madhivanan.

    Hey I doubt is it Madhivanan or Madhavan

    Thanks!
    "He laughs best who laughs last"

  15. Madhivanan Moderator

    &gt;&gt;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
  16. vsnreddi New Member

    Hi.Reddy..if you have any interesting questions and answers please send it to me at vsnreddi@gmail.com Thanks..vsnreddy

    SURYA

Share This Page