Hi, I have an application using Entity Framework. The database was created with cascadeDelete set to true. This is because every 6 months data from the database needs to be purged.

Every couple of months I have to import a spreadsheet (importeddata), run a statement to copy the spreadsheet to a temp table:

insert into ImportTempTable (Column1,Column2          Column3,Column4,Column5,Column6,Column7,Column8,Column9,Column10,Column11,Column12)

select F1, CAST(F2 AS BIGINT), F3, F4, F5, F6, F7, F8, F9, CAST(F10 AS BIGINT), F11, 1

       from importeddata

       where F3 LIKE '_____-___'

I then run the following statements to essentially bring in data from the ImportTempTable into TheTable but remove any duplicates. What I've been noticing is that even though these statements are executed against one main table data is going missing from other tables. For example, TheTable has a FK in TableA. Checking the row count before and after the statements are executed shows a decrease in row count after. I believe this is because the cascadeDelete set on the tables. Is there a way to "ignore" the cascadeDelete when running these statements?

--INSERT new items
INSERT INTO TheTable (Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9, Column10, Column11, Column12)
 select Column1, Column2, Column3, Column4, Column5, Column6, Column7, Column8, Column9, Column10, Column11, 1
  from ImportTempTable as src
  where not exists (
   select 1 from TheTable as dst
    where isnull(src.Column2, '') = isnull(dst.Column2, '')
      and isnull(src.Column3, '') = isnull(dst.Column3, '')
      and isnull(src.Column4, '') = isnull(dst.Column4, '')
      and isnull(src.Column5, '') = isnull(dst.Column5, '')
  )

--DELETE removed items
DELETE src
 from TheTable as src
 where not exists (
  select 1 from ImportTempTable as dst
   where isnull(src.Column2, '') = isnull(dst.Column2, '')
     and isnull(src.Column3, '') = isnull(dst.Column3, '')
     and isnull(src.Column4, '') = isnull(dst.Column4, '')
     and isnull(src.Column5, '') = isnull(dst.Column5, '')
 )

--UPDATE items
update dst
 set Column1 = src.Column1,
  Column6 = src.Column6,
  Column7 = src.Column7,
  Column8 = src.Column8,
  Column9 = src.Column9,
  Column10 = src.Column10,
  Column11 = src.Column11
 from TheTable as dst
 join ImportTempTable as src
 on isnull(src.Column2, '') = isnull(dst.Column2, '')
   and isnull(src.Column3, '') = isnull(dst.Column3, '')
   and isnull(src.Column4, '') = isnull(dst.Column4, '')
   and isnull(src.Column5, '') = isnull(dst.Column5, '')

• Moved by Lydia ZhangMicrosoft contingent staff, Moderator Wednesday, June 24, 2015 7:51 AM more appropriate

Thanks Eric. I'll take another look.

Olaf, as I stated in my first post, there is purge function that deletes data older than 6 months. Cascade delete is set on the tables that are to be purged. However, there is a separate script I have to run that will merge an imported spreadsheet with a table and then delete any duplicates. The duplicates are only in the one table. With cascade delete set on the tables the delete ends up removing data from other tables that SHOULD NOT be removed. That's why I want to "ignore" it in this case. Do you understand now?

You may need to disable your foreign key (with cascade delete set) in the process and recreate it after you finish your clean up.  The cascading delete will have no effect between your tables.

Okay, I give up...how would I disable the foreign key. This app uses the Entity Framework code first migrations which I'm totally new to.

--You need to find the foreign key constraint name of your table  which is doing cascading delete

USE yourdb;
GO
ALTER TABLE yourtable
NOCHECK CONSTRAINT FK_name;
GO

--
--Other operation
--

--at the end activate your foreign key
ALTER TABLE yourtable WITH CHECK CHECK CONSTRAINT FK_name 

Thanks!

When I expand the column tab in management studio the FK is ToInstitutionID. I ran

USE dbname;
GO
ALTER TABLE tablename
NOCHECK CONSTRAINT ToInstitutionId;
GO

I get

Msg 4917, Level 16, State 0, Line 1

Constraint 'ToInstitutionsId' does not exist.

Msg 4916, Level 16, State 0, Line 1

Could not enable or disable the constraint. See previous errors.

When I run

EXEC

SP_HELP'tablename'

The foreign key is listed as

FK_dbo.tablename_dbo.parenttablename_ToInstitutionId for the constraint name and ToInstitutionId for the constraint_keys.

So I used FK_dbo.tablename_dbo.parenttablename_ToInstitutionId in the query and run it I get

Msg 102, Level 15, State 1, Line 2

Incorrect syntax near '.'.

• Edited by rsford31 12 hours 9 minutes ago needed to change table names referenced

 
USE yourdbName;
GO
ALTER TABLE yourtableName
NOCHECK CONSTRAINT [FK_dbo.tablename_dbo.parenttablename_ToInstitutionId];
GO

• Marked as answer by rsford31 8 hours 43 minutes ago

Thanks! That worked. I backed up the db from prod, restored to dev, ran the above script and then proceeded to execute the other scripts. I checked the count of the previously affected tables before and after and they remained equal. Thanks!