Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Using DTS to import / append data from Oracle to SQL2000 without causing Primary key constaint errors

Posted on 2004-08-25
13
Medium Priority
?
671 Views
Last Modified: 2013-11-30
I'm using using DTS in SQL2000 to import data from Oracle. The DTS job is using a straightforward default append statement in ActiveX. However, I'm importing old data each time as well as updated information from Oracle and this predictably causes PK violations. To get around the problem I'm deleting all data from the destination table in SQL prior to each import. This is obviously very inefficent and not scalable.

Is there a way of skipping these errors so that the DTS import job ignores the PK constaints and continues to import new data?
0
Comment
Question by:robcpod
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 6
  • 4
  • 2
  • +1
13 Comments
 
LVL 11

Expert Comment

by:Otana
ID: 11891077
When you select which tables to copy in your DTS, go to "Transform", and select "Drop and recreate destination table". This is faster than deleting all records.

0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11891127
dropping and recreating the table can be a mess - if you have indexes, constraints etc.
Probably the cleanest thing is to create a trigger on the table you are inserting into along the lines of :

create trigger mytrig on mytable
instead of insert
as
begin
     insert into mytable
     select * from inserted
     where not exists (select 1 from mytable where mytable.PK = inserted.PK)
end
0
 

Author Comment

by:robcpod
ID: 11891549
Hi Otana,

That's good to know, however, I'm trying to avoid dropping the table if at all possible...

Hi BillAn1

I'm a novice with databases so forgive my ignorance!

How can I apply the script within DTS? I understand how I can apply the trigger if both source and destination tables are on the same RDBMS, how does it work when the source is on Oracle and the destination is on SQL2000?
0
Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

 
LVL 17

Expert Comment

by:BillAn1
ID: 11891672
The trigger is purely applied to the destination table in SQLServer, it is not applied to the Oracle DB.
It is not something that will be part of the DTS. Once you have applied the trigger it will be in place 'forever'
To apply the trigger, you can use Enterprise manager
select the table you want, right-click ->ALl tasks -> manage triggers
insert the text you want, along the lines I have shown, but obviously customised for your particular table etc
Once it's done, you can run your DTS, and any rows that are already there, the trigger will simply ignore, so the DTS will not get any errors
0
 
LVL 34

Expert Comment

by:arbert
ID: 11895545
Depending on the number of rows, a trigger can kill your performance.  If you really want to reload the table every time, add an Execute sql task to your DTS package and issue a TRUNCATE TABLE yourtablename.....Doesn't drop the table, doesn't delete each row individually....
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11896225
even truncate will have problems if there are any FK's to the table, then you are back to where you started - deleting the table.
0
 
LVL 34

Expert Comment

by:arbert
ID: 11898190
yep, just saw the heading on the question about primary key constraint errors :)
0
 

Author Comment

by:robcpod
ID: 11900054
Hi BillAn1 & Brett,

What I'm struggling to understand is how I can apply a trigger on the destination table in MSSQL when the source is from multiple tables in an Oracle DB...

Taking BillAn1's example code:

create trigger mytrig on mytable
instead of insert
as
begin
     insert into mytable
     select * from inserted
     where not exists (select 1 from mytable where mytable.PK = inserted.PK)
end

The table referenced as 'inserted' is not on the SQL DB but is on the Oracle DB (and it's 9 tables). Can the trigger reference 'foreign' tables from another RDBMS?
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11900464
OK, here's a brief explaination on how triggers work.
A trigger is a piece of code that is attached to a table, and is fired whenever youo try to insert / update rows to the table.
You can have different types of trigger - FOR INSERT means it will fire immedately after an insert happens, FOR INSERT, UPDATE means it will fire after an INSERT or UPDATE has happened etc. In this case, I have suggested INSTEAD OF INSERT - this means that the data does not get inserted into the tables, instead it is inserted into a special table called insert (this happens for all triggers, including after insert ones too) then the trigger is fired. Now, it is up to the trigger to actually insert the data. Hence, the code here can check if the rows already exist in the table. If they do - then it fill ignore them, otherwise it will go ahead and actually insert them.

Externally, you can 'insert' any rows you like to the table - e.g. your DTS can select all rows that are in the Oracle Table and insert them into the SQL DB table. However the trigger will automatically kick out any rows that are already there.

The tirgger is attached to the destination table only, and it automatically rejects any rows that are already in hte table, without generating an error.
0
 

Author Comment

by:robcpod
ID: 11901064
OK, that's very clear and I now understand!

I've applied the following code to the trigger:

CREATE TRIGGER [OWT_Trig] ON [dbo].[Outward_Tkt_fact]
INSTEAD OF  INSERT
AS
begin
     insert into Outward_Tkt_fact
     select * from inserted i
     where not exists (select * from Outward_Tkt_fact as o where o.unique_id = i.unique_id)
end

BUT I'm still getting a PK violation error when I run the DTS package...It should work...??!

Is there anyway to test the trigger to ensure that it is doing what you expect it to?
0
 
LVL 17

Expert Comment

by:BillAn1
ID: 11901288
you can test the trigger easy enough by just doing an insert of a row of data
insert into Outward_Tkt_fact values(1,2,3,4.... whatever)
and then run it again. you should get no error the second time, but the data should not be inserted twice.

As for getting the duplicate error - could there be duplicates in the source data? if so, you may want to change the trigger to do a SELECT DISTINCT if there are duplicate rows. If there are more than one row with the same unique_id, but different values for other cols, you are still in trouble - you need to decide a 'rule' for what to do with these, if this is the case - should you accept the first one? the last one? the "maximum" one etc.
0
 
LVL 17

Accepted Solution

by:
BillAn1 earned 2000 total points
ID: 11901338
Just remembered - if you are using SQLServer as destination, the default setting is to use Bulk Insert. This bypasses the trigger, so the trigger is not fired.
In the DTS, select options and de-select "use fast load"
Now, this will slow down the load!!
A faster approach may be to to a 'fast load' into another, staging table in SQLServer which has no constraints, but is otherwise identical to the target table.

then do a second step which is a SQL Task, of the form
insert into Outward_Tkt_fact
select * from staging_Outward_Tkt_fact
0
 

Author Comment

by:robcpod
ID: 11902134
The fast load was the problem, the trigger is now working. Thank you!

I have considered a staging table, so I have another good reason to adopt one!

Many thanks.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

610 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