Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
cmadison2426
Starting Member
10 Posts |
 Posted - 2006-12-04 : 09:05:13
|
| hello all and thanks for reading this,here's my puzzle:I insert new records from Oracle to SQL and that works perfectly but I realized I was not getting changes made to existing records. Here's an example of what I mean.O.table1 S.table1********* *********TicketID ------> TicketIDStartDate ------> StartDateEndDate ------> EndDateTicketID is an automatic sequential number. StartDate and EndDate are the start and end dates of the ticket. StartDate will always be known since it's when the ticket is created. EndDate has a default value of NULL since it can't be known when the ticket will be closed. When the ticket is closed in the Oracle db the EndDate field is updated. My problem is I can't figure out how to write the query to check for changes and make the necessary updates. I believe that if the EndDate is NULL in S.table1 I want to update the record from O.table1 otherwise no action is taken. I just can't figure out how to make it happen.Don't know if this is helpful but here is the T-SQL I use to grab all new records:USE TrioGODECLARE @sql nvarchar(500)DECLARE @LastRec bigintSET @LastRec = (SELECT TOP 1 ACCOUNT_CONTACT_ID FROM [1 ACCOUNT_CONTACT] ORDER BY ACCOUNT_CONTACT_ID DESC)SET @sql = N'INSERT [1 ACCOUNT_CONTACT] SELECT * FROM OPENQUERY([APX.WORLD], ''SELECT * FROM AXENT.ACCOUNT_CONTACT WHERE ACCOUNT_CONTACT_ID > ' + CAST(@LastRec AS varchar(18)) + ''')'EXEC sp_executesql @sql |
|
|
|
|
|
getting updates from linked Oracle database