| |
|
If this is your first visit, be sure to check out the FAQ by clicking the link above.
You may have to register before you can post: click the register link above to proceed.
To start viewing messages, select the forum that you want to visit from the selection below.
|
 |
07-27-10, 11:52
|
|
Registered User
|
|
Join Date: Jul 2010
Posts: 11
|
|
|
Sync database
|
|
I am look for either an example code or a tool that can synchronize a Progress database through ODBC to MS SQL 2005 database. When it synchronize, it should only add or updates records to the SQL database and not replace the entire database as there are other records/tables.
1. Are there third party utility that can accomplish this?
2. Does SQL 2005 provide any utility that will do this? Stored Procedure? Will T-SQL be able to help with this?
3. Any example of C# or VB code will also work
4. Example of Select statement to do the actual updates will really help
Can someone help point me to the right direction?
Thank you!
|
|
07-27-10, 12:46
|
|
Resident Curmudgeon
|
|
Join Date: Feb 2004
Location: In front of the computer
Posts: 12,606
|
|
So you want something that will do a wholesale copy of every table that is currently in your Progress databases into your Microsoft SQL database? If you have tables A, B, and C in your Progress database and tables A, B, C, X, Y, and Z in your Microsoft database, then you want to completely replace the existing A, B, and C in the Microsoft database with the contents of the A, B, and C from the Progress database without disturbing the existing X, Y, and Z in the Microsoft database?
-PatP
__________________
In theory, theory and practice are identical. In practice, theory and practice are unrelated.
|
|
07-27-10, 16:52
|
|
Registered User
|
|
Join Date: Jul 2010
Posts: 11
|
|
|
|
Actually not the database but it's more like taking new or updated records from the A B C table of the Progress database and merge into A B C table of the Microsoft database. If the key fields are the same, then it should update the records. It should not replace the tables entirely. For example,
A table in Progress Database
Name* Age
------- -----
Jimmy 38
Alice 36
Emily 60
A table in SQL database
Name* Age
------ -----
Alice 21
Jonathan 22
Tiffany 18
When it sync, the SQL database will be updated like this
Name* Age
------- -----
Alice 36
Jonathan 22
Tiffany 18
Jimmy 38
Emily 60
In this case, Name is the key field. Alice's age is updated to 36 from 21 because it has the same key. The rest will simply be merged to the table.
|
|
07-28-10, 16:03
|
|
Registered User
|
|
Join Date: Jul 2010
Posts: 11
|
|
|
|
07-28-10, 16:43
|
|
Registered User
|
|
Join Date: Jul 2003
Location: San Antonio, TX
Posts: 3,611
|
|
This is almost 1-to-1 from BOL:
Code:
if object_id('[Progress.TableA]') is not null
drop table [Progress.TableA]
if object_id('[SQL.TableA]')is not null
drop table [SQL.TableA]
select * into [Progress.TableA] from (
select [Name*] ='Jimmy',[Age] = 38 union all
select 'Alice', 36 union all
select 'Emily', 60
) x
select * into [SQL.TableA] from (
select [Name*] = 'Alice',[Age] = 21 union all
select 'Jonathan', 22 union all
select 'Tiffany', 18
) x
go
merge [SQL.TableA] as target
using [Progress.TableA] as t
on target.[Name*] = t.[Name*]
when matched
then update set target.[Age] = t.[Age]
when not matched by target
then insert ([Name*], [Age]) values (t.[Name*], t.[Age]);
__________________
"The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."
|
Last edited by rdjabarov; 07-28-10 at 16:45.
Reason: formatting
|
07-28-10, 16:57
|
|
Registered User
|
|
Join Date: Jul 2010
Posts: 11
|
|
Thank you! Dumb question, but what is BOL? SQL Server Books Online? Can you point me to the original link?
|
|
07-28-10, 16:59
|
|
Registered User
|
|
Join Date: Jul 2003
Location: San Antonio, TX
Posts: 3,611
|
|
ms-help://MS.SQLCC.v10/MS.SQLSVR.v10.en/s10de_1devconc/html/952595a6-cf1d-4ff5-8927-66f9090cf79d.htm
or
http://technet.microsoft.com/en-us/l.../bb522522.aspx
__________________
"The data in a record depends on the Key to the record, the Whole Key, and
nothing but the Key, so help me Codd."
|
|
07-28-10, 17:24
|
|
Registered User
|
|
Join Date: Jul 2010
Posts: 11
|
|
Thank you so much!!!!!!! You just made my day!!!
Also, how do I connect to a Progress database through ODBC using this? and connect to local MS SQL database?
|
|
07-29-10, 05:12
|
|
www.gvee.co.uk
|
|
Join Date: Jan 2007
Location: UK
Posts: 10,156
|
|
Look up "Linked Servers" in BOL 
|
|
| Thread Tools |
Search this Thread |
|
|
|
| Display Modes |
 Linear Mode
|
Posting Rules
|
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
HTML code is Off
|
|
|
|
|
dBforums
>
Sync database
