custom transformation and broken FK

Hi,
I need to do the following through Enterprise Manager (EM).
I need to copy the structure of all the tables along with their PK, FK
etc and need to copy the data of only select set of tables (not all of
them). I tried doing this in EM but lost the relationship between
tables in the target database.
Here is the example:
TablePK has an indexed column. TableFK has a column which is the
Foreign key to TablePK.
- I create ine DTS package1 in which I transfer both tables as objects
along with their PK & FK, and without data.
- I ceate a second package2 in which TablePK is only transferred, this
time with Data.
- At the end I notice that the relationship between TablePK and TableFK
is broken in the target database.
Does anyone have a clue how do I go about this problem. I am a newbie.
TIA.
You could use the copy objects task in DTS seeing as this is one of the NGs
from which you have asked for a reply.
You could try using SQLDMO to script all the details you require from the
tables you want as well.
Allan Mitchell MCSE,MCDBA, (Microsoft SQL Server MVP)
www.SQLDTS.com - The site for all your DTS needs.
www.SQLIS.com - You thought DTS was good. here we show you the new stuff.
www.konesans.com - Consultancy from the people who know
<aamirghanchi@.yahoo.com> wrote in message
news:1105830284.795794.101640@.c13g2000cwb.googlegr oups.com...
> Hi,
> I need to do the following through Enterprise Manager (EM).
> I need to copy the structure of all the tables along with their PK, FK
> etc and need to copy the data of only select set of tables (not all of
> them). I tried doing this in EM but lost the relationship between
> tables in the target database.
> Here is the example:
> TablePK has an indexed column. TableFK has a column which is the
> Foreign key to TablePK.
> - I create ine DTS package1 in which I transfer both tables as objects
> along with their PK & FK, and without data.
> - I ceate a second package2 in which TablePK is only transferred, this
> time with Data.
> - At the end I notice that the relationship between TablePK and TableFK
> is broken in the target database.
> Does anyone have a clue how do I go about this problem. I am a newbie.
> TIA.
>
|||try DB Ghost http://www.dbghost.com you can copy over schema and select those
tables where you want to copy data and it takes care to propagate objects and
data in the correct order so relationships aren't broken.
regards,
Mark Baekdal
http://www.dbghost.com
+44 (0)208 241 1762
Database change management for SQL Server
"aamirghanchi@.yahoo.com" wrote:

> Hi,
> I need to do the following through Enterprise Manager (EM).
> I need to copy the structure of all the tables along with their PK, FK
> etc and need to copy the data of only select set of tables (not all of
> them). I tried doing this in EM but lost the relationship between
> tables in the target database.
> Here is the example:
> TablePK has an indexed column. TableFK has a column which is the
> Foreign key to TablePK.
> - I create ine DTS package1 in which I transfer both tables as objects
> along with their PK & FK, and without data.
> - I ceate a second package2 in which TablePK is only transferred, this
> time with Data.
> - At the end I notice that the relationship between TablePK and TableFK
> is broken in the target database.
> Does anyone have a clue how do I go about this problem. I am a newbie.
> TIA.
>
|||When I copy all tables as you have done, I script it using SEM...
Select your source database -> All Tasks-> Generate SQL Script... One of the
last tabs gives you the option of taking FK relationships and indexes etc.
It is NOT checked by default..
Make sure you select it and then run the script in QA to create the tables
in the new DB..
For a small number of tables I usually select INTO to move the data, For a
larger number of tables I use DTS...
Wayne Snyder, MCDBA, SQL Server MVP
Mariner, Charlotte, NC
www.mariner-usa.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
<aamirghanchi@.yahoo.com> wrote in message
news:1105830284.795794.101640@.c13g2000cwb.googlegr oups.com...
> Hi,
> I need to do the following through Enterprise Manager (EM).
> I need to copy the structure of all the tables along with their PK, FK
> etc and need to copy the data of only select set of tables (not all of
> them). I tried doing this in EM but lost the relationship between
> tables in the target database.
> Here is the example:
> TablePK has an indexed column. TableFK has a column which is the
> Foreign key to TablePK.
> - I create ine DTS package1 in which I transfer both tables as objects
> along with their PK & FK, and without data.
> - I ceate a second package2 in which TablePK is only transferred, this
> time with Data.
> - At the end I notice that the relationship between TablePK and TableFK
> is broken in the target database.
> Does anyone have a clue how do I go about this problem. I am a newbie.
> TIA.
>
sql