Hello,
We are moving our data from one sql2k to another. The initial db has all
of the tables in a primary filegroup. We have created multiple filegroups
for the new server from scripts. When we try to import the data from the old
sql to the new we get a bunch of FK and PK violations. Is this a way to shut
all of these off for the import and then turn them all back on or do we have
to do each table separately? Thanks in advance.
JohnThe PK violations implies that there is something wrong
with the import. You need to make sure the data added is
correct. Something like a different collation could cause
this.
FK - you just need to import the tables in the correct
order. ANother option is to remove all the FKs and add
them after the import.
Given that you seem to have problems wit hthe data I
would make sure the data is checked when the FKs are
added.
Showing posts with label initial. Show all posts
Showing posts with label initial. Show all posts
Friday, March 9, 2012
Importing data from Oracle
All,
I am working on a proof of concept, and basically trying to import multiple tables of data from an Oracle 8i server to SQL 2000.
The initial import is of all rows in about 7 tables. However, from then on, the business rule would be to import only changed and added rows, let's say every hour. The Oracle tables all have a last modified time on them, so that a query can determine whether the row is changed or added when compared to meta data stored in SQL 2000 if needed.
My questions:
1) What is the best way to do the first import. Currently, I'm thinking DTS?
2) What is the best way of doing the changed/updated row import? So far, I've tried heterogeneous queries, but I'm running into data type problems (meaning that SQL 2000 doesn't recognize some of the data types in the Oracle tables.
Any help here would be greatly appreciated. I've done work with SQL 2000 before, but not much with importing data.
Thanks in advance,
JeremyI'd say I'd just use a linked table, but I might be missing something in what you are trying to accomplish. Hope this helps.|||I would use DTS for that task, Microsoft OLE DB Provider for Oracle - Microsoft OLE DB Provider for SQL Server connections with Transform Data Task where you can run SQL query against Provider for Oracle (in Oracle SQL dialect). I think no problem with that. martin
I am working on a proof of concept, and basically trying to import multiple tables of data from an Oracle 8i server to SQL 2000.
The initial import is of all rows in about 7 tables. However, from then on, the business rule would be to import only changed and added rows, let's say every hour. The Oracle tables all have a last modified time on them, so that a query can determine whether the row is changed or added when compared to meta data stored in SQL 2000 if needed.
My questions:
1) What is the best way to do the first import. Currently, I'm thinking DTS?
2) What is the best way of doing the changed/updated row import? So far, I've tried heterogeneous queries, but I'm running into data type problems (meaning that SQL 2000 doesn't recognize some of the data types in the Oracle tables.
Any help here would be greatly appreciated. I've done work with SQL 2000 before, but not much with importing data.
Thanks in advance,
JeremyI'd say I'd just use a linked table, but I might be missing something in what you are trying to accomplish. Hope this helps.|||I would use DTS for that task, Microsoft OLE DB Provider for Oracle - Microsoft OLE DB Provider for SQL Server connections with Transform Data Task where you can run SQL query against Provider for Oracle (in Oracle SQL dialect). I think no problem with that. martin
Subscribe to:
Posts (Atom)