sql server dts: Salute,

Consider this scenario :
I have a master/detail database,
and another database with the same schema but different data.
Now, how can I ADD the master/detail data from the second DB
to the first one ? how can I resolve the identity conflict ?

If I use DTS with "enable Identity Insert" option,
the old records' identity may conflict with new records' identity.
If I don't use this option, my master records will get new identity
values and I lose relation between my new master data
and new detail data.

Any Ideas?

Regards,

Ok so if I read you correctly you want to import Parent and child rows from
databases into a centralised database but maintain the link from the source
databases in the destination.

No problem.

The way I do this is to have a SourceIdentifier attribute on the
destination. This identifies the row from the source.

So if the Source has an ID attribute that is used to link it to the children
then I would have a destination of something like

CREATE TABLE Dest (id int.............., SourceIdentifier int)

You may need more than one attribute to identify the source system but it
works.

Now what you do is you load the parents first.
You then load the children by looking up the Parent id in the Source and
matching with the SourceIdentifier attribute in the parent table and
returning the ID for that row from the Parent table.

HTH


--

Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
[quoted text, click to view]

Salute ,

Unfortunately I'm not allowed to change
the schema of database and It seems that
I have to write my own script/application
to read and insert data row by row.
Do you know another way ?

Thank you,
Mehdy Mohajery

Hi

Where are the two databases? You may be able to get away with a partitioned
view, this way you could add a column that denoted the source. If you are
just wanting to import the data, then you could either load it into a
staging table that does not have an identity, but will have a FK with
cascading updates, so that when you increase the PK value in the parent
table the child data is automatically updated... or you could use a SQL
Query as the source for your datapump that moves the values into higher
range. You will need to allow indentity_inserts and possible re-seed the
identity once you have finished.

This may be useful
http://www.sqldts.com/default.aspx?293

John


[quoted text, click to view]