Simple SQL Bulk Copy

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

May 2010: New version located at: http://projects.c3o.com/install/sqlcopy201005/sqlcopy.zip

Enhancements:

Filters out computed columns
Ability to edit select sql for each table.
Save copy jobs as xml
Console application to run xml copy jobs from batch file

December 2009: New very beta version that supports saving and reloading copy jobs from xml files. Also has simple console app to run a copy job from command line. You can get it here

Thanks to Dan for suggestion on command line version!

Nov 2009: Started working on Oracle support.

Email if interesting in testing oracle copy.

Sept 2009: Received an enhancement from Matt to "PockyMaster" branch

Code = http://sqlcopy.googlecode.com/svn/branches/2.0.0.0/

Matt: I noticed a problem with the 2.0.0.0 branch of Simple SQL Bulk Copy. If the column ordering of a table in the source database doesn't exactly match the ordering in the destination database table, the SqlBulkCopy object may fail to map the columns properly. It looks like this only happens in 2.0.0.0 because it selects columns from the source table by name, whereas 1.0.0.23 does a "SELECT *". I added a bit of code to explicitly map all the columns by name, which seems to fix the problem.

April 19, 2009 There is a branch based on PockyMaster's code. You can pull it down here:

Code = http://sqlcopy.googlecode.com/svn/branches/2.0.0.0/

Exe = http://projects.c3o.com/install/sqlcopy/simplesqlcopy2.exe

PockyMaster: " Found some issues with computed columns. I changed the code a bit to filter them. I made some other improvements as well: Selection of nr. of simultaneous copy threads, added extra column to show if the object is a table or a view, added quick selection for selection only tables or only views, added progress notification (every 1000 rows or so the status is updated) + showing when all is copied. Further I fixed the crash when the copy button is pressed twice"

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