IMPORT table data using DROP and CREATE table constraints

It’s bit difficult to import data when your tables have constraints on it. It can be achieved easily by dropping the constraints of the tables for which you want to import data but you may not remember the deleted constraints if you want to re-create again once the import process completes.

So it’s better to run the following (create constraints) script first on the database before importing data and save the output script.

-- RECREATE CONSTRAINTS --

SELECT 'ALTER TABLE [' + OBJECT_NAME(f.parent_object_id)+ ']'

+ ' ADD CONSTRAINT ' + '[' +  f.name  +']'+ ' FOREIGN KEY'+'('+COL_NAME(fc.parent_object_id,fc.parent_column_id)+')'

+' REFERENCES ['+OBJECT_NAME (f.referenced_object_id)+']('+COL_NAME(fc.referenced_object_id,

fc.referenced_column_id)+')' as Scripts

FROM .sys.foreign_keys AS f

INNER JOIN .sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

GO

Next step is to run the following (drop constraints) script on the database and copy the output script and paste on the query analyzer and execute.

-- DROP CONSTRAINTS --

SELECT 'IF EXISTS (SELECT * FROM sysobjects WHERE name = ''' + f.name + ''')

BEGIN

      ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+ ' DROP  CONSTRAINT ' + '[' + f.name  + ']

END' 

FROM .sys.foreign_keys AS f

INNER JOIN .sys.foreign_key_columns AS fc ON f.OBJECT_ID = fc.constraint_object_id

GO

Start the import process; once the import process finishes then run the saved create constraints script to re-create all the dropped constraints in the database.