Relationships causing problems 1

[jibberski]

I have an app that uses over 100 flat files that I import via DTS. My routine Drops all the tables in the DB and then imports them.

I recently have begun development on this DB and need to relate some tables. Once I create relationships I can no longer simply drop and import.

So I've been using:
exec sp_msforeachtable "DELETE FROM ?"
to clean out the data and leave the relationships alone. This works great.

However, what doesn't work is the import. How can I simply clean out the DB and run my import leaving the relationships intact?


Thanks for your help!

 

[TomKane]

Hi,

It may be a sequencing thing. If, for example, you have a master table and a (for want of a better term) a sub table you should import the master table first and then the sub table.

Also as an alternative to DELETE FROM you can also use TRUNCATE TABLE

Regards,
Tom

 

[jibberski]

I'll try moving things around..

Thanks for the truncate tip. That released some zoom into the routine.

 

[AlexCuse]

Jibberski - I think that TomKane is right. One thing I will add is that you may run into problems using truncate if foreign keys are involved. The ordering may help this out though.



Ignorance of certain subjects is a great part of wisdom

 

[jibberski]

YUP - the truncate failed on the tables with the F keys...

 

[jibberski]

How can I temp remove the keys, do the import, and replace the keys ?

 

[TomKane]

Hi,

Hmmmm... that's a pity.... but I still think you'll be OK if you get your sequence right.

Regards,
Tom

 

[AlexCuse]

you can use delete from on the tables with foreign keys, and truncates on the rest. That is what I use for a similar process to what you describe.

I have never scripted anything with foreign keys, but if you look up ALTER TABLE in BOL you should be able come up with something.

Ignorance of certain subjects is a great part of wisdom

 

[jibberski]

On the Sequence of things???

If table A is Dependent on B and C

should I import B and C then A or vice versa?

 

[AlexCuse]

You want to truncate/delete in order ABC

import in CBA

Ignorance of certain subjects is a great part of wisdom

 

[jibberski]

Thanks guys - Got it all working!