I have several tables in a database that I want to restore from another database on another server.
I have used the Import data SQL tool to bring a copy of the tables onto the destination server. My plan was to truncate the tables and then use a SELECT * FROM SOURCE INTO DESTINATION. This failed because the tables contain foreign keys and other constraints.
Is there a quick way to refresh this data or do I need to drop all of the constraints and then re-apply once the data has been copied?
What I can think of is to disable your FK and perform the import of data for all tables, once you have done that, enable your FK again then it should be alright. While you doing that, you might disable the indexes as well, it will speed up the import, you will just need to rebuild the indexes afterwards.
You do have to ensure that the table FK is in place after you import the data.
Script to disable FK
ALTER TABLE [table name] NOCHECK CONSTRAINT all