The Need: I have to copy the data in tables belonging to a schema user from one database in SQL Server 2005 to another. Note: the target table has all the indexes and constraints already, and I just wanted to refresh the data from source to target.
The Problem: The export/import wizard fails miserably when trying this task due to all the indexes and constraints on the target SQL tables (these are tables for a vendor software application which uses different schema users vs different databases for separating dev/test/prod environments). Additionally, I tried using SQL Server 2008 R2 SSMS's generate script functionality to generate both schema and data according to this
post. This resulted in a 8GB file which failed when I ran sqlcmd with it on the target server. It seems to me there's some bug with either the generate script wizard or SQLCMD in dealing with strings that span multiple lines.
The errors when running SQLCMD were:
Msg 105, Level 15, State 1, Server
, Line
Unclosed quotation mark after the character string ...
Msg 102, Level 15, State 1, Server , Line
Incorrect syntax near ...
The solution: I ended up doing the following:
1. Run script to generate SQL to drop all the tables belonging to a schema user on the target database. (this will drop all the dependent indexes/constraints etc)
select
'DROP table [' + table_schema + '].[' + table_name + ']'
from
information_schema.tables
where table_schema = 'mySchema'
2. run the script generated from step 1 on target server to drop the tables.
3. Run export/import wizard in SQL Server 2005 SSMS to copy all the desired tables from source to target database
4. Run SQL Server 2005 SSMS to generate schema script of all the desired tables in source database, making sure to script all the indexes, constraints, and also script the if exists option.
5. Run the script generated from step 4 on target database to recreate the indexes and constraints.
Hope this helps!
