Solved

Develop a automated script to copy data from one DB table to the same table in another DB

Posted on 2014-01-14
5
266 Views
Last Modified: 2014-02-05
Platform
1: Windows server 2008 R2
2: SQL server 2008 R2

Hi,

I have 2 server environments (ENV-A and ENV-B)  running SQL server 2008 R2 and I would like to copy records from DB_A.dbo.table1(ENV-A) and append them to the DB_B.dbo.table1(ENV-B) every 15mins.  

Problem:
How to copy data between DBs over the network every 15 mins.
How to append records if the SS# data field in table 1 on ENV-B is null.

Please share with me any articles / examples about how to copy records from one DB to another DB.

Thanks in advance.
0
Comment
Question by:cesemj
  • 3
5 Comments
 
LVL 15

Accepted Solution

by:
tim_cs earned 500 total points
ID: 39780553
Have you looked into using replication?

If that is not an option can you provide the table schema?  Are you wanting to just move new inserts or are their updates and deletes as well?  You mention something about the SS#, is that the primary key?
0
 

Author Comment

by:cesemj
ID: 39780829
I have not looked into replication but I will.  What I am tying to accomplish is anytime a record is added it table1 in ENV-A the record is automatically copied to table1 in ENV-B only if the record does not exist or have the SS# (social security number) filled in.  

Table Schema
AwardID                              int                             
FirstName                           nvarchar(100)      
LastName                           nvarchar(100)      
EmailAddress                           nvarchar(100)      
PhoneNumber                   nvarchar(50)      
AwardOption                      nvarchar(10)      
Fax                                           nvarchar(50)      
OrgCNo                                   nvarchar(50)      
OrgName                           nvarchar(200)      
OrgType                             nvarchar(50)      
OrgTypeII                          nvarchar(100)      
ProfessionalDesc                   nvarchar(200)      
Other                                   nvarchar(300)      
BusinessAddress                   nvarchar(MAX)      
City                                           nvarchar(100)      
State                                   nvarchar(10)      
ZipCode                                   nvarchar(10)      
regAwardDate                   datetime      
AwardNo                           nvarchar(3)      
regConfirmationDate           nvarchar(50)      
Status                                   nvarchar(15)      
regAttendance                   nvarchar(15)      
Note                                        nvarchar(MAX)
SS#                                           nvarchar(6)      
StatusReason                           nvarchar(MAX)      
AwardNoStatus                   nvarchar(3)
0
 
LVL 42

Expert Comment

by:EugeneZ
ID: 39781223
if you need  the near 'real-time" solution
and data be available to "read"  on the "subscriber" ,
as per above post use replication :

data on the 2nd server will be almost immediately changed if on the 1st it was changed

Transactional Replication can be your choice, just make sure the table has  PK
http://technet.microsoft.com/en-us/library/ms151176(v=sql.105).aspx
0
 

Author Comment

by:cesemj
ID: 39781414
Thanks , I will review and let you know.
0
 

Author Closing Comment

by:cesemj
ID: 39835458
Thanks
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
Via a live example, show how to set up a backup for SQL Server using a Maintenance Plan and how to schedule the job into SQL Server Agent.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

773 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