April 2, 2013 at 2:35 pm
I am using SSIS to import data, but there is problem of Forignkey constraints.
So I think I need to drop all the FKs, then truncate table, import data then add the FK back.
Is there an easy way to generate drop and create FK statements other than using SSMS?
Thanks
April 2, 2013 at 2:49 pm
Could run a SQL task in SSIS to do it. Just write a script to do it and have SSIS run that script or scripts.
April 2, 2013 at 2:51 pm
Erin Ramsay (4/2/2013)
Could run a SQL task in SSIS to do it. Just write a script to do it and have SSIS run that script or scripts.
Thanks, my question is how to generate a script for dropping and recreating all the FKs in the database?
Thanks
April 2, 2013 at 2:54 pm
If your data is violating your FK constraints, which from the sounds of your post it is, dropping the constraints to import the data isn't going to help. You will still have data in violation of the FK constraints.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 2, 2013 at 2:57 pm
Sean Lange (4/2/2013)
If your data is violating your FK constraints, which from the sounds of your post it is, dropping the constraints to import the data isn't going to help. You will still have data in violation of the FK constraints.
They are not violating the Keys in a way -when they are importing they need to import data from certain orders of the table.
But to figure out the orders is pretty much work, so that is why we do above process.
April 2, 2013 at 2:57 pm
i would consider disabling and re-enabling them instead; either way it's effectively the same thing:
select
'ALTER TABLE '
+ QUOTENAME(schema_name(schema_id))
+ '.'
+ quotename(name)
+ ' NOCHECK CONSTRAINT ALL' as SQLDisable,
'ALTER TABLE '
+ QUOTENAME(schema_name(schema_id))
+ '.'
+ quotename(name)
+ 'WITH CHECK CHECK CONSTRAINT ALL ' As SQLEnable
from sys.tables
Lowell
April 2, 2013 at 3:00 pm
Lowell (4/2/2013)
i would consider disabling and re-enabling them instead; either way it's effectively the same thing:
select
'ALTER TABLE '
+ QUOTENAME(schema_name(schema_id))
+ '.'
+ quotename(name)
+ ' NOCHECK CONSTRAINT ALL' as SQLDisable,
'ALTER TABLE '
+ QUOTENAME(schema_name(schema_id))
+ '.'
+ quotename(name)
+ 'WITH CHECK CHECK CONSTRAINT ALL ' As SQLEnable
from sys.tables
But I read somewhere you still cannot truncate the tables after you disabled FKs, you have to use delete , by using delete, I have to also figure out the order of tables of doing that.
April 2, 2013 at 3:02 pm
sqlfriends (4/2/2013)
Lowell (4/2/2013)
i would consider disabling and re-enabling them instead; either way it's effectively the same thing:
select
'ALTER TABLE '
+ QUOTENAME(schema_name(schema_id))
+ '.'
+ quotename(name)
+ ' NOCHECK CONSTRAINT ALL' as SQLDisable,
'ALTER TABLE '
+ QUOTENAME(schema_name(schema_id))
+ '.'
+ quotename(name)
+ 'WITH CHECK CHECK CONSTRAINT ALL ' As SQLEnable
from sys.tables
But I read somewhere you still cannot truncate the tables after you disabled FKs, you have to use delete , by using delete, I have to also figure out the order of tables of doing that.
It seems you just need to understand the order that you need to work with your data here.
When importing, first import the parent data, then import the child data.
When deleting you do it in reverse. First delete the child data, then you can delete the parent data.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 2, 2013 at 3:04 pm
If I use drop FK,truncate table, import data, then add FKs,
Then I don't need to figure out the order of the tables.
April 2, 2013 at 3:05 pm
i missed the truncating the table part.
there's a lot of script contributions, it looks like, that do exactly that.
some of them are scripts that ASSUME your foreign key is on a single column, but technically the constraint can be across multiple columns, so be aware if you have what i would call exotic foreign keys:
http://www.sqlservercentral.com/search/?q=script+foreign+keys
Lowell
April 2, 2013 at 3:06 pm
sqlfriends (4/2/2013)
If I use drop FK,truncate table, import data, then add FKs,Then I don't need to figure out the order of the tables.
If you are going to go that route why not just have your FKs cascade? Then you don't have to figure out the order and you don't have to drop and recreate all the FKs. Seems to me it would be as much or more work to script dropping and recreating all the FKs than just ordering your deletes.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 2, 2013 at 3:26 pm
Sean Lange (4/2/2013)
sqlfriends (4/2/2013)
If I use drop FK,truncate table, import data, then add FKs,Then I don't need to figure out the order of the tables.
If you are going to go that route why not just have your FKs cascade? Then you don't have to figure out the order and you don't have to drop and recreate all the FKs. Seems to me it would be as much or more work to script dropping and recreating all the FKs than just ordering your deletes.
Speed. Raw speed.
Cascading deletions take longer. I do the same thing (heck, it's part of why I avoid RI except in particular circumstances). These are truncate/reload table scenarios, usually done in non-SOR scenarios. The keys are usually there not to clean inputs but either because ERWin (or other) included them or they want their diagrams to build cleanly.
Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.
For better assistance in answering your questions[/url] | Forum Netiquette
For index/tuning help, follow these directions.[/url] |Tally Tables[/url]
Twitter: @AnyWayDBA
April 2, 2013 at 3:31 pm
Evil Kraig F (4/2/2013)
Sean Lange (4/2/2013)
sqlfriends (4/2/2013)
If I use drop FK,truncate table, import data, then add FKs,Then I don't need to figure out the order of the tables.
If you are going to go that route why not just have your FKs cascade? Then you don't have to figure out the order and you don't have to drop and recreate all the FKs. Seems to me it would be as much or more work to script dropping and recreating all the FKs than just ordering your deletes.
Speed. Raw speed.
Cascading deletions take longer. I do the same thing (heck, it's part of why I avoid RI except in particular circumstances). These are truncate/reload table scenarios, usually done in non-SOR scenarios. The keys are usually there not to clean inputs but either because ERWin (or other) included them or they want their diagrams to build cleanly.
I know what you mean but the OP said they want to do this so they don't have to figure out the order to delete the data. Sounded to me as though they thought this seems like the easy way to go. When I hear things like drop all FKs in the database and then recreate them it puts up a red flag.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
April 2, 2013 at 3:55 pm
Using delete will also create big transaction log data. The tables we are working on have huge records. So using delete is not a good choice for us.
April 2, 2013 at 5:21 pm
The script on the link below can be used to determine the reference levels of all tables in a database in order to be able to create a script to load tables in the correct order to prevent Foreign Key violations.
Find Table Reference Levels
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply