Listing Parent tables and child table

Question

Listing Parent tables and child table

vinayak.v

SSCrazy

Points: 2414
More actions
September 20, 2011 at 6:47 am

#248167

Hi ..
I have two database name AA and AAA..
AA database contains the data ...
and AAA its a empty database containing same tables
in both the database..
now i want to insert the values from
AA to AAA but... first i need to insert the parent
table and then child table ...
I want to create a new table containing
the parent table first and then child table ...
so that i can insert the values easily ..
or is there any option to do inserting in empty database ...
can anybody help to do this...

Viewing 10 posts - 1 through 9 (of 9 total)

You must be logged in to reply to this topic. Login to reply

PaulB-TheOneAndOnly SSC Guru Points: 66772 More actions · Answer 1

vinayak.v (9/20/2011)
Hi ..
I have two database name AA and AAA..
AA database contains the data ...
and AAA its a empty database containing same tables
in both the database..
now i want to insert the values from
AA to AAA but... first i need to insert the parent
table and then child table ...
I want to create a new table containing
the parent table first and then child table ...
so that i can insert the values easily ..
or is there any option to do inserting in empty database ...
can anybody help to do this...

If you are looking to move ALL tables from AA to AAA... force restore a current backup of AA database on top of AAA database 🙂

If you are looking to move SOME tables from AA to AAA... disable all FK constraints, move the data, re-enable FK constraints. Since data is kosher on AA no reason to believe it will get screwed up when moved to AAA - on top of it, inserts will work faster with no referential integrity in place at refresh time.

_____________________________________
Pablo (Paul) Berzukov

Author of Understanding Database Administration available at Amazon and other bookstores.

Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.

vinayak.v SSCrazy Points: 2414 More actions · Answer 2

Thanks for the reply ..

Actually the scenario is i'm using merge statement for the

sync process where i have to give table name of parent first

and then child ..

that's y i need the parent table list and also child...

steveb. SSC-Forever Points: 46733 More actions · Answer 3

use T-SQL insert into

or

SSIS

from one db to the other..

vinayak.v SSCrazy Points: 2414 More actions · Answer 4

But ... how to enable and disable the foreign key in script ...

Lowell SSC Guru Points: 323518 More actions · Answer 5

you can use sp_fkeys [ParentTable] to generate a list of child table references;

from there you can use that list to script your merge statements...does that help?

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

steveb. SSC-Forever Points: 46733 More actions · Answer 6

vinayak.v (9/20/2011)
But ... how to enable and disable the foreign key in script ...

Dont bother with this, they are there for a reason just insert the data the in correct order to maintain RI,

vinayak.v SSCrazy Points: 2414 More actions · Answer 7

Thanks Steveb ...

Is there any process to sync two database

except replication, third party tool ... ?

steveb. SSC-Forever Points: 46733 More actions · Answer 8

vinayak.v (9/20/2011)
Thanks Steveb ...
Is there any process to sync two database
except replication, third party tool ... ?

I always use SQL data compare from Redgate , excellent tool and well worth the ££

rgtft Hall of Fame Points: 3518 More actions · Answer 9

If the database is small enough, you could script out all of the data -- right click on DB in SSMS and chooses Tasks, Generate Scripts. Then run that script against the second db (AAA) to insert it there. Your referential integrity will still be fine.

Rob