import data from Oracle 8i and constraints lost

[mkey]

Hi all,
One of our clients complained that when they imported data from Oracle 8i to SQL Server 2000 the constraints got lost.
Is this possible?

Thanks!

 

[SQLSister]

It's not only possible; it's likely. This is not to say you can't set these things up in the data import, but you have to take extra steps to do so. When you import data all you get by default is the data, anything else you need like constraints, primary keys, indexes etc, you would have to set up in the import or beforehand in the SQL table you are importing to. The reason for this is that often the data being imported will not use the same constraints as the original data. Sometimes this is because the data is intended to be read-only - there will be no data entry except from the imports, so no need for the constraints. Also the data is often used differntly in a different system, so the existing constraints may or may not be what you want. So it is up to whoever designs the DTS package to specify what is needed in the import.

 

[mkey]

Hi,
Thank you for the response. NOw how can I set up the DTS so that I can import all the constraints?

Thanks!

 

[SQLSister]

I would do this in the DTS package only if you are going to be dropping and recreating the table every time you import. Otherwise I would do this directly on the SQL tables.

I presume you can script the Oracle tables. I would do this to find out what the constraints are, then convert the script to alter the SQL table(s). You can then run this in Query analyzer if you only need to do it once or put it in your DTS package as an Execute SQL task after the task which creates the table.