Data Import or Update Operations on tables with constraints

If table has constraints, SQL’s data import wizard will throw error while importing data from another table or from test database to prod database.

In these cases, it is possible to disable constraints for particular tables. List of constraints can be queried with the script given below.

SELECT
OBJECT_NAME(constraint_object_id) ‘Constraint Name’
FROM sys.foreign_key_columns
WHERE OBJECT_NAME(referenced_object_id) = ‘YourTableName’
GO

Output will look like below:

After querying list of tables and constraint names for a table, below script is edited for each constraint. (Excel can be used to prepare script in order to disable all referencing constraints at once)

ALTER
TABLE YourTableName NOCHECK
CONSTRAINT ConstraintName

It is ok now to use SQL Import data wizard to import or update table.

After having completed import/update operation, constraints have to be enabled back to prevent data independency between related tables.

ALTER
TABLE YourTableName CHECK
CONSTRAINT ConstraintName

*ATTENTION* If datas imported don’t meet constraint conditions, it will not be possible to enable constraint back, therefore it is suggested to import all related tables (especially if it is aimed to import prod from test database) together, then enable constraints back.

Leave a Reply

Your email address will not be published. Required fields are marked *