Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Relationships causing problems 1

Status
Not open for further replies.

jibberski

Programmer
Aug 24, 2005
52
US
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!
 
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
 


I'll try moving things around..

Thanks for the truncate tip. That released some zoom into the routine.
 
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
 


YUP - the truncate failed on the tables with the F keys...
 
How can I temp remove the keys, do the import, and replace the keys ?
 
Hi,

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

Regards,
Tom
 
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
 

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?
 
You want to truncate/delete in order ABC

import in CBA

Ignorance of certain subjects is a great part of wisdom
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top