TRUNCATE TABLE query unable to execute in SSIS because of foreign key

Question

I have a table Person which contains 2 fields.In my another database i have a Participant Table(also have 2 columns).From Participant Table i have to insert values into Person Table. but before every insertion,i want truncate the person Table.

I have try it out with linking Execute Sql task to Data flow task.But it is showing error that a Primary Foreign key relation exists there.

Truncate table person---It is written in Execute sql task.This Sql Task is linked with Data flow task which is used for inserting data b/w participant table to person table — manoj kumar singh, Mar 23 '12 at 07:39
possible duplicate of [Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?](http://stackoverflow.com/questions/253849/cannot-truncate-table-because-it-is-being-referenced-by-a-foreign-key-constraint) — Pondlife, Mar 23 '12 at 13:08

score 6 · Answer 1 · edited May 23 '17 at 11:51

6

If I understand correctly, SSIS has nothing to do with your real problem, which is that you want to truncate a table that is referenced by a foreign key constraint. That question has already been answered: Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?

edited May 23 '17 at 11:51

Community

1
1

answered Mar 23 '12 at 13:07

Pondlife

15,992
6
37
51

rlobban · Accepted Answer · 2012-03-23T16:49:49.203

4

If a table in sql server has foreign key references then you can't truncate. instead in your execute sql task use delete without a where clause.

delete from person_table

If you are really adamant about truncating the table, you could drop the foreign key constraints, then truncate the table then recreate the foreign key constraints. Providing of course, that the user you are running the package as has the privileges to do so.

edited Mar 23 '12 at 16:49

answered Mar 23 '12 at 07:40

rlobban

343
1
7

1

But i want to truncate data before inserting new one.Can anybody tell me the way to achive this in SSIS – manoj kumar singh Mar 23 '12 at 09:06
by deleting from the table without providing a where clause will give the same effect as truncating. the difference is that delete will cause more logging than a truncate. – rlobban Mar 23 '12 at 16:35

score 1 · Answer 3 · edited Mar 25 '12 at 11:42

1

Create an "Execute SQl" task and run DELETE FROM person after this task, run your import.

DELETE FROM will give the same result as TRUNCATE TABLE, but if the table has a foreign key pointing to it, it cant be truncated. You have to use the delete command

edited Mar 25 '12 at 11:42

cairnz

3,917
1
18
21

answered Mar 23 '12 at 09:52

Diego

34,802
21
91
134

score 0 · Answer 4 · answered Aug 25 '15 at 11:37

0

In SSIS Transfer SQL Server Objects Task Set Property DeleteFirst to TRUE

answered Aug 25 '15 at 11:37

Muflix

6,192
17
77
153

score 0 · Answer 5 · answered Mar 27 '12 at 20:22

You won't be able to delete either unless cascading deletes is turned on (or you delete the child records first). Why is this a problem you ask, why can't I do what I want to do? Because if you do then you may lose the integrity of the data. Suppose I have records in table2 which relate to records in table 1. Suppose further that table1 has an autogenerated id. If I could truncate that table, then I leave those records i ntable 2 hanging out without any record to reference them, they have become orphaned. Well but I'm putting the data back in you say. But then they will have new id numbers and you will still lose the relatinoship tothe related data.

Can you drop the FK and truncate and insert and recreate the FK. Yes you can but it is a poor practice and you should not unless you are also recreating those related records.

The best practice is to use a MERGE statement to update or insert depending on what you need.

TRUNCATE TABLE query unable to execute in SSIS because of foreign key

5 Answers5