Script Example Needed To Export/Import Tables With FK's In Sequence

HELLO...

We have a number of Tables, that need to be "migrated" from one database to another, in MSSQL-2012.  There are FK Dependencies on a good number of these tables.  For example:

PS_TKT_HIST_LIN                   (Table-# 1)
PS_TKT_HIST_DISC_COD       (Table-# 2)
PS_TKT_HIST                           (Table-# 3)
PS_TKT_HIST_LIN_CELL         (Table-# 4)
PS_TKT_HIST_LIN_CELL_EXT (Table-# 5)

...and so on.  In the example above, Table-# MUST exist FIRST...then...Table-# 2 MUST exist SECOND, then Table-#1, Table-#4, and Table-# 5.  I know we can use the built-in MSSQL EXPORT/IMPORT function...and that's great...but, because these need to be Exported/Imported IN SEQUENCE (due to the FK Dependencies), we figured we'd have to do that "Write a query to specify the data to transfer"...as opposed to..."Copy data from one or more tables or views".

Based on the above example, therefore, can someone please help us to develop the correct script with syntax, to accomplish this task?  It would be unbearable to do this 1-table-at-a-time, as there are over 200 tables, with FK Dependencies.  If we can get an example to provide a roadmap, that would be great.  And before you ask...the COMMAND LINE utilities are not an option...this MUST be done, within Management Studio :-)

Thank you in advance...Mark
datatechcorpAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>>  It would be unbearable to do this 1-table-at-a-time, as there are over 200 tables, with FK Dependencies.

If you have a lot of FK's in place, then I would suggest to do the below items:
1. Create a script to Drop all Foreign Keys and Create all Foreign keys as individual files with IF EXISTS condition.
2. Execute the Drop all Foreign key scripts in the destination.
3. Use Import/Export Wizard to transfer all data to the destination database
4. Create all Foreign keys in place. If any fails, then you can simply rerun it since it has IF EXISTS condition.
Scott PletcherSenior DBACommented:
Below is a function I created that returns a "dependency_level" for all tables.  Tables at level 1 must be loaded before tables at level 2, which must be loaded before tables at level 3, etc..

USE [your_db_name];
SET ANSI_NULLS ON;
SET QUOTED_IDENTIFIER ON;
GO
CREATE FUNCTION dbo.Get_Tables_With_Dependency_Level ()
RETURNS TABLE
AS
/* --SELECT * FROM dbo.Get_Tables_With_Dependency_Leve] () ORDER BY dependency_level, schema_name, table_name
 */
RETURN (
    WITH cte_tables AS
    (
        SELECT
            OBJECT_SCHEMA_NAME(t.object_id) AS schema_name,
            OBJECT_NAME(t.object_id) AS table_name,
            t.object_id AS object_id,
            1 AS dependency_level
        FROM
            sys.tables AS t
        WHERE
            t.is_ms_shipped = 0
        UNION ALL
        SELECT
            OBJECT_SCHEMA_NAME(t.object_id),
            OBJECT_NAME(t.object_id),
            t.object_id,
            ct.dependency_level + 1
        FROM
            sys.tables AS t
        INNER JOIN sys.foreign_keys AS f ON 
            f.parent_object_id = t.object_id AND
            f.parent_object_id != f.referenced_object_id
        INNER JOIN cte_tables AS ct ON 
            f.referenced_object_id = ct.object_id
        WHERE
            t.is_ms_shipped = 0
    )
    SELECT dependency_level, schema_name, table_name, object_id
    FROM (
        SELECT 
            ct.dependency_level,
            ct.schema_name,
            ct.table_name,
            ct.object_id
            , ROW_NUMBER() OVER(PARTITION BY ct.schema_name, ct.table_name ORDER BY ct.dependency_level DESC) AS row_num
        FROM
            cte_tables AS ct
    ) AS derived
    WHERE
        row_num = 1
)
GO

Open in new window

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> Below is a function I created that returns a "dependency_level" for all tables.

To clarify more, Author was asking how to use Generate Scripts wizard to generate data for the tables having Foreign keys.
I understand your script will give you the tables with foreign key order but I'm afraid that can't be used with Generate Scripts wizard while generating the INSERT scripts..
Since they have around 200+ tables, it would be difficult to run Generate scripts wizard that much times to get the INSERT scritps generated for its data..

Hi datatechcorp,

A feedback would be appreciated for our comments so that we can guide you better..
Announcing the Winners!

The results are in for the 15th Annual Expert Awards! Congratulations to the winners, and thank you to everyone who participated in the nominations. We are so grateful for the valuable contributions experts make on a daily basis. Click to read more about this year’s recipients!

Scott PletcherSenior DBACommented:
@Raja

I didn't read their request that way.  When the OP wrote:
we figured we'd have to do that "Write a query to specify the data to transfer"...as opposed to..."Copy data from one or more tables or views".  Based on the above example, therefore, can someone please help us to develop the correct script with syntax, to accomplish this task?
I figured they'd be happy with a sequence of tables they can use in their own set of queries to copy the data.  That's easy enough to do in T-SQL, you don't need a wizard just for that.
datatechcorpAuthor Commented:
RAJA & SCOTT...

Thank you both soooooo much for chiming in here!  We took the advice from both of you, and now, the data is migrated.  Sorry for not responding yesterday, but was so doggone busy...slammed...with this migration chore.  We very much appreciate both of you...thanks so much!

Mark
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Welcome, glad to help..

>> I figured they'd be happy with a sequence of tables they can use in their own set of queries to copy the data

Yes, good point and Nice collaboration between us!!
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Query Syntax

From novice to tech pro — start learning today.