0

This is my requirement, I am trying to elaborate that in terms of Adventure works database.

I have to delete all records of Production.Product table which has constraints and Foreign key relation and fill the data from other table (currently I am using this statement to copy data in the table)

SELECT * INTO Product_temp
FROM [AdventureWorks].[Production].[Product]

So I have to delete all records from Product table and then insert from product_temp.

I have found this answer, but I am unable to find this option in SQL SERVER 2005

SQL Server 2005 Cascading Delete

Community
  • 1
  • 1
Zerotoinfinity
  • 6,290
  • 32
  • 130
  • 206
  • Well, you cannot just delete stuff away if it's being referenced by a foreign key - that's the whole point of having FK relationships! So what you need to do is: drop all FK constraints referencing the `Product` table, then delete all the rows and insert the new ones. But what are you going to do with data that might be refencing a product that doesn't exist anymore after you've inserted the new data??? – marc_s Feb 12 '12 at 15:44
  • @marc_s In my project, we are bringing fresh data from SAP for product table and my customer wants to delete all records of all 4 (in my project 4 tables are master table) master table and insert fresh data again. Please let me know your opinion on this, could this be in a better way? I will suggest the same to my client. – Zerotoinfinity Feb 12 '12 at 15:47
  • 1
    Are you totally replacing all rows?? Or do you have lots of rows that would stay the same, or just change in one or two columns?? In that case, it would probably be smarter to just "upsert" the Products table - update what needs updating, insert new rows, remove the few rows that need to be removed - instead of doing a sledge-hammer "delete everything" approach.... – marc_s Feb 12 '12 at 16:55

2 Answers2

1

You don't need to remove the foreign key constraint.

  1. Fire the delete query .Then

2.EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'

3.Exec sp_MSforeachtable "dbcc dbreindex('?')"

pranav aggarwal
  • 201
  • 3
  • 5
1

If you are able to do a wholesale replace of all four tables then your quickest strategy is to delete all rows from all four table and re-insert rather than doing an "Upsert" (good term @marc_s). One recommendation there: I'd recommend using TRUNCATE to clear out your tables rather that DELETE. This performs much better because the engine avoids writing all those deleted records into the transaction log. Obviously you'd need to delete your "fringe" tables first, that ones that have FK's to other tables.

If you find that the tables have circular references that make it hard to delete rows from tables then here's what I'd do:

  1. Drop all constraints from your tables
  2. Truncate your target tables (sequence won't matter since your constraints are gone)
  3. Import the data (use bcp if you can for fastest loading!)
  4. Re-apply constraints
  5. (Optional) Update statistics on the target tables
sisdog
  • 2,649
  • 2
  • 29
  • 49