[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 284
  • Last Modified:

sql 2005 query for pulling records

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.
1 Solution
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
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.
romeiovasuAuthor Commented:
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.

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Then u need to write ur own Logic To solve the Issue.
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.
romeiovasuAuthor Commented:
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.
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

NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now