Error while export tables data only from production database to test server database.

While exporting all tables’ data only from one database in production server to test server database, the error I got as shown below.

TITLE: Microsoft.SqlServer.DtsTransferProvider

------------------------------

s description=Executing the query "TRUNCATE TABLE [database_name].[dbo].[tablename]

" failed with the following error: "Cannot truncate table ‘[database_name].[dbo].[tablename]’because it is being referenced by a FOREIGN KEY constraint.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

 helpFile= helpContext=0 idofInterfaceWithError={8BDFE893-E9D8-4D23-9739-DA807BCDC2AC}

For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&LinkId=20476

------------------------------

BUTTONS:

OK

Solution:

1. Run below scripts in the destination database and save the both scripts for future use.

n      
-- DROP CONSTRAINTS  
-- 
SELECT 'ALTER TABLE ' + '[' + OBJECT_NAME(f.parent_object_id)+ ']'+ 
' DROP  CONSTRAINT ' + '[' + f.name  + ']' 
FROM .sys.foreign_keys AS f 
INNER JOIN .sys.foreign_key_columns AS fc 
ON f.OBJECT_ID = fc.constraint_object_id 
 
 
--  
-- 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 

2. Then Run the saved output of DROP CONSRAINTS scripts to drop the all constraints.

3. Then truncate all tables by below method.

Use destinationdatabasename

Go

sp_tables

Copy all table_name column values and paste it in excel sheet.

Then join ‘TRUNCATE TABLE ‘with all tables in another excel column as like below

 Eg:

="TRUNCATE TABLE  " &A1 &" "

Table_name

TRUNCATE TABLE  Table_name

4. Then run the saved output of RECREATE CONSTRAINTS scripts to recreate all the dropped constraints.

5. Finally run the export and import wizard to copy all the tables’ data with no issue.

I hope you can do export with no issue.