Hi friends,
In my project I have redesigned my database structure. In the existing
structure there is no Primary key and no relationship b/w data.
In the new structure Primary key and the relationship is added.
Now I want to migrate the existing data into the new structure.
There is a chance for duplicate records and also records that does not
satisfy referential integrity.
How to migrate the data? I want to have a copy of the duplicate records and
also the records which does not satisfy referential integrity.
Its a huge database, so i can't query table by table to find the mismatch
records.
How to proceed?
thanks
vanithaHi,
Let me know can you consider the following:
I assume that you have two database one is normalized and other one is not
normialized.
1. Backup the Both Databases.
2. Add err_text column to all tables in non-normolized DB.
3. Write a store procedure with the following logic:
a. Select a record and try inserting to Normolized DBs table ,
if no error occurs delete the same from Non-Normolized table,
otherwise
updated err_text column of respective table in non-normolized db
with the
error
b. perform step (a) for each record in every table in non-normlized DB.
4. After the step3 what ever record exists in table of non-normolized db
either they
are passed Primary or referential integrity.|||Vanitha
There are many approaches to this. some are very trickey too.
1)One thing that you can easily do is to update new tables and compare with
old one find unmatched rows using pimary key.( u can use dynamic query to
supply table names while comparing)
2) You can use sp_MSforeachtble: You have to write something dynamically to
abtain all duplicate rows
3) write a procedure (using cursor perhaps) to take columns from
information.schema.columns where for each table and supply those columns to
find out duplicat rows using group by and count(*) >1 as you usually do.
4) Use a sproc to insert row by row to another table and insert into
errortable whereever there is an error using @.@.error <>. This approach gives
coplete rows for which inserts failed.
but 2 and 3 only gives duplicate rows and does not check other constriants
Hope This will give you a lead. though does not solve
Regards
R.D
"Vanitha" wrote:
> Hi friends,
> In my project I have redesigned my database structure. In the existing
> structure there is no Primary key and no relationship b/w data.
> In the new structure Primary key and the relationship is added.
> Now I want to migrate the existing data into the new structure.
> There is a chance for duplicate records and also records that does not
> satisfy referential integrity.
> How to migrate the data? I want to have a copy of the duplicate records an
d
> also the records which does not satisfy referential integrity.
> Its a huge database, so i can't query table by table to find the mismatch
> records.
> How to proceed?
> thanks
> vanitha
>|||I can't add a column to the existing table.
Can I add a new table into the existing database called "errorTable" and the
n
insert the table_name, column data and the error description.
pls advice on this.
thanks
"SlowLearner" wrote:
> Hi,
> Let me know can you consider the following:
> I assume that you have two database one is normalized and other one is not
> normialized.
> 1. Backup the Both Databases.
> 2. Add err_text column to all tables in non-normolized DB.
> 3. Write a store procedure with the following logic:
> a. Select a record and try inserting to Normolized DBs table ,
> if no error occurs delete the same from Non-Normolized table,
> otherwise
> updated err_text column of respective table in non-normolized db
> with the
> error
> b. perform step (a) for each record in every table in non-normlized DB
.
> 4. After the step3 what ever record exists in table of non-normolized db
> either they
> are passed Primary or referential integrity.
>|||there are millions of records, so I can't select each and every record and
check the errors.
I want to do this in bulk.
pls help me to solve this.
thanks
vanitha
"SlowLearner" wrote:
> Hi,
> Let me know can you consider the following:
> I assume that you have two database one is normalized and other one is not
> normialized.
> 1. Backup the Both Databases.
> 2. Add err_text column to all tables in non-normolized DB.
> 3. Write a store procedure with the following logic:
> a. Select a record and try inserting to Normolized DBs table ,
> if no error occurs delete the same from Non-Normolized table,
> otherwise
> updated err_text column of respective table in non-normolized db
> with the
> error
> b. perform step (a) for each record in every table in non-normlized DB
.
> 4. After the step3 what ever record exists in table of non-normolized db
> either they
> are passed Primary or referential integrity.
>|||why dont you create a new table or temp table with the same syntax and add
all uninserted columns data to new error table
Regards
R.D
"R.D" wrote:
> Vanitha
> There are many approaches to this. some are very trickey too.
> 1)One thing that you can easily do is to update new tables and compare wi
th
> old one find unmatched rows using pimary key.( u can use dynamic query to
> supply table names while comparing)
> 2) You can use sp_MSforeachtble: You have to write something dynamically t
o
> abtain all duplicate rows
> 3) write a procedure (using cursor perhaps) to take columns from
> information.schema.columns where for each table and supply those columns t
o
> find out duplicat rows using group by and count(*) >1 as you usually do.
> 4) Use a sproc to insert row by row to another table and insert into
> errortable whereever there is an error using @.@.error <>. This approach giv
es
> coplete rows for which inserts failed.
> but 2 and 3 only gives duplicate rows and does not check other constriants
> Hope This will give you a lead. though does not solve
> Regards
> R.D
>
> "Vanitha" wrote:
>|||There are n number of records... so i can't chk record by record.
is there any way to do in bulk. and if any error during the operation, the
migration shd not be cancelled, it shd log in another table or something lik
e
that.
thanks
vanitha
"R.D" wrote:
> Vanitha
> There are many approaches to this. some are very trickey too.
> 1)One thing that you can easily do is to update new tables and compare wi
th
> old one find unmatched rows using pimary key.( u can use dynamic query to
> supply table names while comparing)
> 2) You can use sp_MSforeachtble: You have to write something dynamically t
o
> abtain all duplicate rows
> 3) write a procedure (using cursor perhaps) to take columns from
> information.schema.columns where for each table and supply those columns t
o
> find out duplicat rows using group by and count(*) >1 as you usually do.
> 4) Use a sproc to insert row by row to another table and insert into
> errortable whereever there is an error using @.@.error <>. This approach giv
es
> coplete rows for which inserts failed.
> but 2 and 3 only gives duplicate rows and does not check other constriants
> Hope This will give you a lead. though does not solve
> Regards
> R.D
>
> "Vanitha" wrote:
>|||TRY THIS
CAN YOU JUST INSERT INTO NEW TABLE AND COMPARE WITH OLD TABLE SO THAT YOU
KNOW WHAT RECORDS ARE NOT INSERTED
this is like this
1) add indentity column in the old table and new table
2) insert into new table2( normalised)
3) insert rows in an order
4) find out which rows are not available in new table using somethinglike
SELECT * FROM TABLE1 LEFT OUTER JOIN TABLE2 ON TABLE.IDENTITY =
TABLE2.IDENTITY WHERE TABLE2.IDENTITY IS NULL
I think this is simple and possible
Regards
R.D
REGARDS
R.D
"Vanitha" wrote:
> There are n number of records... so i can't chk record by record.
> is there any way to do in bulk. and if any error during the operation, the
> migration shd not be cancelled, it shd log in another table or something l
ike
> that.
> thanks
> vanitha
> "R.D" wrote:
>|||OOPS
IN THE SECOND TABLE IDENTIY COLUMN SHOULD ALSO BE INSERTED EXLICITLY OR MAKE
IT A NUMERIC.
sorry there is caps lock pressed on my key board
R.D
"Vanitha" wrote:
> There are n number of records... so i can't chk record by record.
> is there any way to do in bulk. and if any error during the operation, the
> migration shd not be cancelled, it shd log in another table or something l
ike
> that.
> thanks
> vanitha
> "R.D" wrote:
>|||thanks a lot
if any error exits during migration, then it stops the migration process.
how to continue the migration even if error occurs.
thanks
vanitha
"R.D" wrote:
> OOPS
> IN THE SECOND TABLE IDENTIY COLUMN SHOULD ALSO BE INSERTED EXLICITLY OR MA
KE
> IT A NUMERIC.
> sorry there is caps lock pressed on my key board
> R.D
> "Vanitha" wrote:
>
