SQL Server Performance

stored procedure to sync two table

Discussion in 'General DBA Questions' started by manu_dba, Jan 26, 2007.

  1. manu_dba New Member

    hi sql gurus..

    can anyone please guide me that how i can write a stored procedure to sycn two tables ...

    suppose i have two tables a and b both in same database having two columns

    table a
    columns id int
    descrip varchar(100)

    table b
    columns id int
    descrip varchar(100)

    now i have 100 records in 50 records in b
    can anyone please help me in writing stored procedure to sunc these two tables.
    both the tables are in the same database

    i have to use that stored procedure for 10 tables in similar fashion.

    thanks a lot
  2. Chappy New Member

    Are there any rows in table A with the same id as B ?
    Im not sure what it is you want to do, just copy all rows from B into A and vice versa?

    I dont really understand why you want to do this, especially as they are both in the same database.

    Maybe you could create a view instead

    Create View dbo.merged
    AS
    SELECT id, descript from a
    UNION ALL
    SELECT id, descript from b


  3. manu_dba New Member

    Thanks a lot chappy for your response.

    actually in table A every morning data will come

    and when i will run the stored procedure the data which is not in the table b will get transfer to the table b.

    so i need to create a store procedure that can help me to sync these two tables and i cannot create the view the one you suggested.

    please let me know if you can provide me any guidance to go forward

  4. khtan New Member

    insert where not exists


    insert into tableb (id, descrip)
    select id, descrip
    from tablea a
    where not exists (select * from tableb b where b.id = a.id)


    How about update from tablea to tableb ? Do you need that ?


    KH
  5. vermamunshi New Member

    Hi Manu,

    I think you can keep the tables in sync with each other, by writing a TRIGGER (for INSERT) on your so-called Table A which gets data in the morning and which needs to be moved to Table B. As soon as a row will get inserted to Table A, it'll be moved to Table B too, at the same time.
    But make sure your Table B's structure is in tandem with Table A.

    If you want me to write the trigger for you, I'll do that.



    Thanks and regards,
    Munshi Verma
    verma_munshi@yahoo.com

    "I Wish I Had Born Without A HEART, For It Gets Hurt..."
  6. MohammedU New Member

    If the data inserting into tableB is not time sensitive, it is better to use script prvided by Khtan and schedule it to run every 5/10 minutes instead of trigger...


    MohammedU.
    Moderator
    SQL-Server-Performance.com

Share This Page