**UPDATE (5/18/2017) **
I discovered the problem isn't as big as I thought it would be. This ONLY occurs after a schema has been deployed, and the migration errors out during the initial phase. The only time a schema will ever have to be deployed is if there are new or modifications to the new database. Then once the migration tool begins, it must be successful in populating the whole database (which takes around 20 mins), so # of errors * the time it takes to finish = total time to fix all bugs, which can take several hours. However, it's a bit disconcerting if this ever happens. If the migration is successful, and no modifications are made to the schema, then the below scenario wouldn't be troubling. These modifications don't happen often, maybe once or twice a month. I'm still interested in other kinds of solutions to mediate this though. But this problem took a serious amount of time to understand whats going on.
**ORIGINAL POST (5/17/2017) **
I have reviewed the following related Stack Overflow questions, but none have answered what I'm going through.
Why setting current identity value is not working for me in SQL Server 2008 R2?
Cannot truncate table because it is being referenced by a FOREIGN KEY constraint?
BACK STORY
I have been tasked with migrating a database, involving the restructuring of tables, making sure it has data integrity, and simplifying the schema. This migration is performed by c# application, which calls stored procedures, adds business logic, etc. Every morning at 5 am, there is a task scheduler that clears out all data, except the auditing tables, and retrieves all refreshed data (in case original data has been updated). The migration tool is idempotent, so that the database can be deleted and recreated, and after running the migration tool, everything is back to normal (with refreshed data). Also the migration tool imports relevant data by reference tables first, to make sure no violations of constraints occurs, and it also picks off on those index IDs, rather than any old legacy ID. Also every table has IDENTITY (1,1)
In order to make sure the database is refreshed, I have to perform a DELETE FROM operation. Truncating tables does not work since there would be violations of foreign key constraints. So the way I'm doing it, is by turning off constraint, deleting from the data, turning constraints back on, and reseeding the value.
So when my program runs, it executes the following lines:
sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
sp_MSForEachTable 'DELETE FROM ?'
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'"
EXEC sp_MSforeachtable 'DBCC CHECKIDENT(''?'', RESEED, 0)'
However, if the database instance is a fresh install, and I run the migration tool, and it errors out, there will be problems, as some tables won't be populated. As a few tables have had records inserted, while other tables has no records inserted; what this means, is if I run the above code again in my migration tool, the next seed values of the tables that has records inserted will be ID=1, whereas the seed value of the untouched tables will be null (and the first insert will start off as ID=0). This is because this overwrites IDENTITY (1,1). If I was to run a DBCC CHECKIDENT on tables where no records were inserted, such as
DBCC CHECKIDENT ('TABLEA', NORESEED)
You get the following:
Checking identity information: current identity value 'NULL', current column value 'NULL'.
No matter what you do, you can't reseed an identity value of 'NULL' to any other number.
This became a serious issue, since each time the migration tool runs, I keep running into foreign key constraint violations because there are certain indexes which doesn't exist in the referenced tables. This also has a domino effect, where if one table has been indexed improperly (ID=0), all tables referencing that table will violate constraints - skipping out on the import, and indexes on those tables will be ID=0 as well the next time we run the migration tool.
HACK SOLUTION:
I expect all tables to start off from ID=1, not 0. So as of now, the only possible way to make sure this runs, is to wipe out the database from a clean slate (delete and recreate), comment out the four command codes above, run the migration tool, hope it is successful in populating all tables, go back to the code, uncomment the four command codes, and deploy the tool into the task scheduler.
QUESTION
Is there any way that DBCC CHECKIDENT can return a value that my c# application can recognize, or perhaps find/ignore all the tables that hasn't been inserted yet so I can reseed only the ones that has been inserted? Or should I try to keep track of what tables ran, and see if the migration tool can ignore those tables on the next run? If that's the case, what's the best way to go around doing this?