sql 2005 query for pulling records

Posted on 2009-12-20
Last Modified: 2012-05-08
HI All,

i have Database A and Database B. i have tables in Database A When ever any record is inserted or updated it has to be updated in to tables which is Database B.

i cannot create triggers on Database A due to warranty issues. how can i pull the records from the table from database a to tables into database b.

can some one help me this is very urgent for me.
Question by:romeiovasu
    LVL 8

    Expert Comment

    Hi romeiovasu,

    you didn't specify in details if the tables are the same, is it a sync job you are trying to pull:

    you have the following options:

    - replication

    - create a job that will run every 10 minutes

    again you didnt specify enough details

    you might need to create Integration Services Project as well, it might be considered as an option
    LVL 10

    Expert Comment

    Assuming the tables are similar I'd suggest you create a unique identifier column in both tables and use it as a key and then link one server to the other. Then you will have to create job that will do all the updates.

    Because Uniqueidentifier field is globally unique you can just copy data without worrying about duplicated keys. To make things more efficient you should mark records already synced and those that need to be copied or updated.

    It would be easiest if the record has last updated timestamp column or you could create one.
    If not depending on columns you could add computed column with a checksum and compare them.

    Author Comment

    no those are not same tables i have to just pull those records and add it to my staging database and push into my master tables.

    Database B is going to my staging database.

    i will give my real time.

    we have Database A, Database B, Database C, Database D.

    all databases above i cannot create triggers because of warranty issues.

    and i have a staging database. where i have to pull the records from the above database when ever any records are inserted or updated. and add the company id those records and send it to staging database.

    LVL 8

    Expert Comment

    Then u need to write ur own Logic To solve the Issue.
    LVL 10

    Expert Comment

    Yes, you have to implement your own logic but you can still use jobs to execute it.
    Try linking servers and creating stored procedures first, and then creating a scheduled job.

    Author Comment

    yes i have already linked and i got the logic too. but can some one provide me a sample script how to get those new records which has got inserted or updated.
    LVL 10

    Accepted Solution

    well, that's the hard part if you don't have the dateupdated column or a checksum and it all depends on actual data.
    You have to come up with a way to check what has been added and updated.

    If you use same key, the new records are simple

    insert into second.dbo.table
    select <here actual column list with all the modifications and computations>
    from primary.dbo.table as T1
    where T1.primaryKeyColumn not in (select primaryKeyColumn from second.dbo.table)

    now the updates, if there is no single field to compare, you need to compare all the fields for changes like that

    update T2 set
       column1 = T1.column1,
       column2 = T1.column2,
       columnX = T1.columnX
    from second.dbo.table as T2
    inner join primary.dbo.table as T1 on T1.primaryKeyColumn = T2.primaryKeyColumn
       t1.column1 <> t2.column1
       or t1.column2 <> t2.column2
       or t1.columnX <> t2.columnX

    Featured Post

    Looking for New Ways to Advertise?

    Engage with tech pros in our community with native advertising, as a Vendor Expert, and more.

    Join & Write a Comment

    Suggested Solutions

    Composite queries are used to retrieve the results from joining multiple queries after applying any filters. UNION, INTERSECT, MINUS, and UNION ALL are some of the operators used to get certain desired results.​
    How to leverage one TLS certificate to encrypt Microsoft SQL traffic and Remote Desktop Services, versus creating multiple tickets for the same server.
    how to add IIS SMTP to handle application/Scanner relays into office 365.
    Access reports are powerful and flexible. Learn how to create a query and then a grouped report using the wizard. Modify the report design after the wizard is done to make it look better. There will be another video to explain how to put the final p…

    755 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    22 Experts available now in Live!

    Get 1:1 Help Now