A customer at work requested something that at first seemed like a no-brainer, even for someone who’s SQL mojo is as weak as mine. He has two SQL 2000 databases, one for production use, and one for preview (integration) use, and he wanted to have a scheduled job periodically copy the data from production to preview. The key was that he wanted only the data to be copied; all other objects should be left alone. My first thought was, “Surely there’s a built-in capability to do just this.” Well, not exactly.
First Try
First I tried the Copy Database Wizard, but it requires the source and target databases to be on separate SQL Server instances. No joy there.
Second Try
Data Transformation Services (DTS) has a Transform Data Task which will do this at a table level, but there are 50 or 60 tables in the database, and I didn’t want create all those tasks. That wasn’t going to work.
Third Time’s the Charm (not!)
Further investigation into DTS revealed the Copy SQL Server Objects Task, which allows copying of sets or subsets of objects between databases. That’s what I was looking for. I set up the task for to copy the data from all tables in the source database to the destination database. In the Copy tab, I configured the task to copy only the data in all of the tables, to not create the tables, and to replace the existing data.
Perfect…until I actually tried executing the task and got the following error:
Cannot truncate table ‘<tablename>’ because it is being referenced by a FOREIGN KEY CONSTRAINT
After whacking the keyboard and muttering expletives, I did a little digging and was able to come up with what was billed as a solution for this problem: disable constraint checking before truncating the DBs, do the copy, then re-enable constraint checking. The easiest way is to loop through all the tables and execute the following command for each one:
ALTER TABLE <table_name> NOCHECK CONSTRAINT ALL
Turning constraints back on was a matter of using the same loop, executing the following command for each one:
ALTER TABLE <table_name> WITH CHECK CHECK CONSTRAINT ALL
It did what it was billed to do, but it didn’t solve the problem. I still got the error when executing the copy objects task.
Ahh, Finally!
So I did some more digging, and finally I came up with another way to do this - don’t truncate the tables, delete the data in them using the following SQL command:
DELETE <table_name>
So we still need to disable and re-enable the table constraints, but the process works.
So, Putting It All Together…
From the above information, I created the following DTS workflow:
1. DB Connection: Create an OLE DB Connection to the target database
2. Disable Table Constraints: Create an Execute SQL Task associated with the above connection and with the following SQL code:
set nocount on
declare @table sysname,
@cmd nvarchar(1000)
declare Curs_Tables cursor static for
select name
from sysobjects
where xtype='U'
open Curs_Tables
fetch next from Curs_Tables into @table
while (@@fetch_status=0)
begin
select @cmd = 'ALTER TABLE [' + @table + '] NOCHECK CONSTRAINT ALL'
exec (@cmd)
fetch next from Curs_Tables into @table
end
close Curs_Tables
deallocate Curs_Tables
GO
Workflow: On success, go to step 3.
3. Delete Table Data: Create an Execute SQL Task associated with the above connection and with the following SQL code:
set nocount on
declare @table sysname,
@cmd nvarchar(1000)
declare Curs_Tables cursor static for
select name
from sysobjects
where xtype='U'
open Curs_Tables
fetch next from Curs_Tables into @table
while (@@fetch_status=0)
begin
select @cmd = 'DELETE [' + @table + ']'
exec (@cmd)
fetch next from Curs_Tables into @table
end
close Curs_Tables
deallocate Curs_Tables
GO
Workflow: On success, go to step 4.
4. Copy Table Data: Create a Copy SQL Server Objects Task, setting the source and destination databases. In the Copy tab, configure the settings as follows:
a. Uncheck the Create destination objects checkbox
b. Check the Copy data checkbox, and select Append data
c. Check the Use collation checkbox (depends on your needs)
d. Uncheck the Copy all objects checkbox. Click on the Select Objects button and select all tables.
e. Check the Use default options checkbox (should work for most needs)
Workflow: On completion, go to step 5.
5. Re-enable Table Constraints: Create an Execute SQL Task associated with the above connection and with the following SQL code:
set nocount on
declare @table sysname,
@cmd nvarchar(1000)
declare Curs_Tables cursor static for
select name
from sysobjects
where xtype='U'
open Curs_Tables
fetch next from Curs_Tables into @table
while (@@fetch_status=0)
begin
select @cmd = 'ALTER TABLE [' + @table + '] WITH CHECK CHECK CONSTRAINT ALL'
exec (@cmd)
fetch next from Curs_Tables into @table
end
close Curs_Tables
deallocate Curs_Tables
GO
That should do the trick. Please note that there’s no exception handling here, so if tasks fail, there’s no handling of the condition. This is really just a framework.
I am using this today for a customer database, and it works as a scheduled job. I believe this makes a reasonable framework for this task if you have special requirements. I’d love some feedback on this…
