How to deal with table relationships where table is deleted and Inserted (1 Viewer)

darbid

Registered User.
Local time
Today, 14:37
Joined
Jun 26, 2008
Messages
1,428
Access 2003 front end - SQL Server 2000 back end.

I will soon receive 3 tables in Excel that I have to add to SQL Server.

These tables are related and will also have other tables related to them.

I will have to delete all rows from the existing 3 tables and then insert all information from excel.

My problem is I am not sure where to start with relationships. I will surely get an error if I delete all rows if relationships exist.

So what do I do. I am hopeing that from Access I can remove the relationship - delete - insert and add the relationships back.

Is this correct?

edit: This is not a one time event, this is going to happen at least every quarter so I am hoping to automate this for users to do so that I do not have to do it.
 
Last edited:

HiTechCoach

Well-known member
Local time
Today, 08:37
Joined
Mar 6, 2006
Messages
4,357
I normally only define a relationship in the relationship window if I want to use enforce Referential Integrity (RI).

Since in your case you will not be able to use Referential Integrity (RI), there really is no need to define the relationship in the Relationship Window. The only advantage you would gain by defining the relationship without RI is that Access will automatically create a join in the query designer.
 

SQL_Hell

SQL Server DBA
Local time
Today, 13:37
Joined
Dec 4, 2003
Messages
1,360
You can use drop constraint to delete relationships.

Or SSIS has options to handle this for you
 

darbid

Registered User.
Local time
Today, 14:37
Joined
Jun 26, 2008
Messages
1,428
Thank you guys for your answers. I must admit I do not understand them, but maybe I do not fully understand this side of things enough yet.

I am tied down doing some other things at the moment, but I will be back as I will have to do this soon.

thanks

David
 

WayneRyan

AWF VIP
Local time
Today, 13:37
Joined
Nov 19, 2002
Messages
7,122
darbid,

This can get quite complex.

In an ideal world, you can:

1) DISABLE the constraints.
2) Delete/Import the data
3) ENABLE the constraints

However, when you hit step 3, due to RI, you might not be able to ENABLE the constraints.

Then the whole automated process will grind to a halt and some human will have to fix the
data before you can re-enable the constraints.

You probably should consider importing into a seperate set of tables, analyzing the new
data and Inserting/Updating the "real" data. That way any "exceptions" can be easily
flagged for the manual corrections before reprocessing.

Wayne
 

darbid

Registered User.
Local time
Today, 14:37
Joined
Jun 26, 2008
Messages
1,428
Thank you Wayne, I understand what you have written.

Basically I am getting the information from another database so I am almost 100% sure that there should not be any problems but you can never be 100% sure. I suppose I could do a join to test that there are no problems with the constraints before adding them again.

In any case I am waiting on "the other database" to give me an example of what they can deliver before I start work on this.

I am sure I will be back.

regards

Davdi
 

Users who are viewing this thread

Top Bottom