Data Migration

Hi friends,
I have changed the existing database scripts by adding primary key and
foreign key constraints.
Now i have created the new structure, I want to mograte 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 create a populate data from the existing DB and how to avoid the
errors?
thanks a lot
vanithaYou have to eliminate the duplicate records, do you want to delete and
discard them, or do you want to keep them ? You have to provide further
informatione / DDL to help you.
Hth, jens Suessmeyer.
http://www.sqlserver2005.de
--|||hi vanitha,
--You can use this code to scan duplicate keys
use northwind
select orderid from [order details]
group by orderid
having count(orderid)>1
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Vanitha" wrote:

> Hi friends,
> I have changed the existing database scripts by adding primary key and
> foreign key constraints.
> Now i have created the new structure, I want to mograte 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 create a populate data from the existing DB and how to avoid the
> errors?
> thanks a lot
> vanitha|||How to generate the populate script?
thanks
vanitha
"Jose G. de Jesus Jr MCP, MCDBA" wrote:
> hi vanitha,
> --You can use this code to scan duplicate keys
> use northwind
> select orderid from [order details]
> group by orderid
> having count(orderid)>1
> --
> thanks,
> --
> Jose de Jesus Jr. Mcp,Mcdba
> Data Architect
> Sykes Asia (Manila philippines)
> MCP #2324787
>
> "Vanitha" wrote:
>|||Hi
You may want to check the data first to see if this does apply:
e.g. for duplicates
SELECT A.col1, A.col2, A.col3
FROM MyTableA
GROUP BY A.col1, A.col2, A.col3
HAVING COUNT(*) > 1
For a foreign key try something like:
SELECT A.col1, A.col2, A.col3
FROM MyTableA A
WHERE NOT EXISTS ( SELECT * FROM FKTable F WHERE A.col2 = F.col1 )
If you want to eliminate the duplicates when inserting the data use a
DISTINCT clause
INSERT INTO NewTable ( col1, col2, col3 )
SELECT DISTINCT A.col1, A.col2, A.col3
FROM MyTableA
To eliminate those that do not have FKs
INSERT INTO NewTable ( col1, col2, col3 )
SELECT A.col1, A.col2, A.col3
FROM MyTableA A
WHERE EXISTS ( SELECT * FROM FKTable F WHERE A.col2 = F.col1 )
John
"Vanitha" <Vanitha@.discussions.microsoft.com> wrote in message
news:DA29DC9C-1109-491C-9970-E69EBAAB16AC@.microsoft.com...
> Hi friends,
> I have changed the existing database scripts by adding primary key and
> foreign key constraints.
> Now i have created the new structure, I want to mograte 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 create a populate data from the existing DB and how to avoid the
> errors?
> thanks a lot
> vanitha|||insert into destination(orderid,x,y,z) --> this are the insert hint
select orderid,x,y,z from orderdetails -->inserted must match
where orderid not in
(
select orderid from [order details]
group by orderid
having count(orderid)>1
)
process those that are duplicate and insert it afterwards
thanks,
Jose de Jesus Jr. Mcp,Mcdba
Data Architect
Sykes Asia (Manila philippines)
MCP #2324787
"Vanitha" wrote:
> How to generate the populate script?
> thanks
> vanitha
> "Jose G. de Jesus Jr MCP, MCDBA" wrote:
>