Simple SQL Bulk Copy

fred.sobel — Mon, 19 Nov 2007 02:19:08 GMT

Sept 10 2008: Just added the source to google code:

svn checkout http://sqlcopy.googlecode.com/svn/trunk/

Spotted this blog post on the SQL Server 2005 Import/Export issue with nice PDF Workaround document. Checkout the PDF for a step by step description on how to export your schema and data.

New Version: May 03 2008 - Added edit form for pre and post sql. Changed default post sql to include "WITH CHECK" per Mat's comment below. Use the SQL button next to the Delete option to edit the defaul pre and post sql.

Pre-SQL

exec sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all';
exec sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all';

Post-SQL

exec sp_msforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all';
exec sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all';

New Version: April 2 2008 - Added options for Check Constraints, Fire Triggers, Table Lock (instead of row level) and Delete Rows:

Warning! The Delete Rows option will attempt to delete rows for the tables you seelct in the destination database. As long as you don't have Check Constraints or Fire Triggers selected it will try to disable Constraints and Triggers before attempting to delete. There is an annoying warning message in case you don't read this.

Here is the complete delete SQL:

-- Disable Constraints for all tables
exec sp_msforeachtable 'ALTER TABLE ? NOCHECK CONSTRAINT all'
exec sp_msforeachtable 'ALTER TABLE ? DISABLE TRIGGER all'

-- Delete From each selected table before copying data:
delete from foo

-- Turn constraints and triggers back on
exec sp_msforeachtable 'ALTER TABLE ? CHECK CONSTRAINT all'
exec sp_msforeachtable 'ALTER TABLE ? ENABLE TRIGGER all'

Update - Feb 8 2008 - The more I used this utility the more I got tired of typing in connection strings as I switched between databases so I added a dropdown for source and destination. It will remember them as they are added. I ran into some problems with the initial update but they should be fixed now. Let me know if you run into problems. You can always go to Control Panel > Add or Remove Programs > Simple Sql Copy and "Restore the Application to it's previous state" to get back to the old version.

Added Timeout and Batchsize
Added error chekcing..etc.
Converted to a Click Once Install to keep versions updated
Post your feedback here

After spending a day fighting with identity columns and the SQL Server 2005 Import/Export Wizard I learned about SqlBulkCopy from a post on David Hayden's blog and created this quick app to get around the problem.
It will copy data between identical tables and keep identity and null columns intact. Used in conjunction with the generate script wizard you can make a complete database copy as long as you have sql access.

Here is the issue

Some posts about it:

Tips for Troubleshooting SQL 2005 SSIS

SQL Server 2005: Import / Export reset identity keys… no workaround… 2005 sucks period, the end

If you have problems with the Click Once install you can pull the exe down from:

http://projects.c3o.com/install/sqlcopy/SimpleSqlCopy.exe

You can always send me email at fcsobel@yahoo.com if you have any questions.

Plugins for 0.2.2.0

fred.sobel — Thu, 11 Jan 2007 03:38:25 GMT

My Torrents, PodCasts, uTorrent and Azureus for Mediaportal 0.2.2.0

Plugins for 0.2.0.0

fred.sobel — Thu, 11 Jan 2007 03:37:13 GMT

My PodCasts, My Torrents, My Azureus and My Utorrent plugins for Mediaportal