469,649 Members | 1,458 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,649 developers. It's quick & easy.

Table order when inserting data

sam
I have to import data into a empty database, that has many tables.
some tables have to be inserted first than others due to the foreign
keys.

How do I find out the order of the tables that I have to insert data
into?

Thanks in advance!

Sam

Jul 23 '05 #1
6 1568
"sam" <sa*****@hotmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
I have to import data into a empty database, that has many tables.
some tables have to be inserted first than others due to the foreign
keys.

How do I find out the order of the tables that I have to insert data
into?

Thanks in advance!

Sam


You need to insert to each parent table before the tables that reference it
with foreign keys. Do you have a database diagram? If not you can generate
one in Enterprise Manager and identify the dependency order from that.

--
David Portas
SQL Server MVP
--
Jul 23 '05 #2
"sam" <sa*****@hotmail.com> wrote in message
news:11**********************@o13g2000cwo.googlegr oups.com...
I have to import data into a empty database, that has many tables.
some tables have to be inserted first than others due to the foreign
keys.

How do I find out the order of the tables that I have to insert data
into?

Thanks in advance!

Sam


You need to insert to each parent table before the tables that reference it
with foreign keys. Do you have a database diagram? If not you can generate
one in Enterprise Manager and identify the dependency order from that.

--
David Portas
SQL Server MVP
--
Jul 23 '05 #3

You can check for Table dependencies in EM

best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
---------------------------------------

*** Sent via Developersdex http://www.developersdex.com ***
Jul 23 '05 #4

You can check for Table dependencies in EM

best Regards,
Chandra
http://groups.msn.com/SQLResource/
http://chanduas.blogspot.com/
---------------------------------------

*** Sent via Developersdex http://www.developersdex.com ***
Jul 23 '05 #5
sam
I have too many tables in the database that I could not check the
denpency one by one. I am looking for a SQL statement that looks
through the system tables such as sysobjects and others to return the
table names in the proper order.

Jul 23 '05 #6
sam (sa*****@hotmail.com) writes:
I have too many tables in the database that I could not check the
denpency one by one. I am looking for a SQL statement that looks
through the system tables such as sysobjects and others to return the
table names in the proper order.


First do this:

SELECT 'ALTER TABLE ' + object_name(parent_obj) +
' NOCHECK CONSTRAINT ' + name
FROM sysobjects
WHERE xtype = 'F'
ORDER BY object_name(parent_obj)

Copy and paste into query window and run. This disables all foreign keys.

Second, bulk load your files in any order.

Third, run:

SELECT 'ALTER TABLE ' + object_name(parent_obj) +
' WITH CHECK CHECK CONSTRAINT ' + name
FROM sysobjects
WHERE xtype = 'F'
ORDER BY object_name(parent_obj)

This reenables the constraints and also checks that data satisfies the
constraints. If you change WITH CHECK to WITH NOCHECK, the constraints
will be enabled without checking. However, in this case, SQL Server
will mark the constraint as "not trusted", which means that the optimizer
will not consider them when building query plans.

(And yes that syntax is really WITH CHECK CHECK.)

I should also add that this is good for Sam, because he has an empty
database. If you have a similar problem for a live database, you need
to be more discriminate, if there are other operations going on in
parallel with your bulk-load.

--
Erland Sommarskog, SQL Server MVP, es****@sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp
Jul 23 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

3 posts views Thread by traceable1 | last post: by
1 post views Thread by Jay | last post: by
2 posts views Thread by binary-nomad | last post: by
9 posts views Thread by Oonz | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.