projects.c3o.com

My Projects
Welcome to projects.c3o.com Sign in | Join | Help
Search
Home Forums Files

Browse Folders

MediaPortal
Plugins (3)

Simple SQL Bulk Copy

File Details
Downloads: 3335 File Size: 5.4kB
Posted By: fred.sobel Views: 10771
Date Added: 18 Nov 2007

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.

If you feel like donating:)

Comments
 

Carso said:

This program works!
29 Nov 2007 4:45 AM
 

David said:

Am I being thick - when I open the download (SimpleSqlCopy.application - 6k) I get "Cannot start application. The application is missing required files". Contact application vendor for assistance"
29 Nov 2007 5:33 AM
 

David said:

Ah - was using firefox - file needs to be downloaded with IE only
29 Nov 2007 5:47 AM
 

guci said:

Nice app, better than the original MS Wizard :) But it won't work with tables with a schema other than dbo. You should use [schema].[tablename] in the query.
3 Dec 2007 1:48 PM
 

Matt said:

This looks like a solution I would love to try, but I get the error "Cannot download the application. The application is missing required files. contact application vendor for assistance." I get the error no matter if I download it in IE or FF.
3 Dec 2007 2:49 PM
 

fred.sobel said:

Matt: You can pull the exe down from here: http://projects.c3o.com/install/sqlcopy/SimpleSqlCopy.exe
3 Dec 2007 11:44 PM
 

Matt said:

Thanks Fred, it works great. One minor suggestion would be to allow the user to view the full stack trace (maybe by clicking on the error or something), when a table does not successfully copy. Mine was simply because the schema was slightly different, but there may be cases where it isn't so obvious why a copy failed. Otherwise, it works absolutely great, thank you very much!
4 Dec 2007 11:45 AM
 

fred.sobel said:

Guci, Changed it to [table_schema].[table_name] using information_schema.tables
4 Dec 2007 10:55 PM
 

jobejufranz said:

Excellent app fred.sobel. Neat and Simple. I'm sure a lot of DB Admins/Developers will find it very useful
11 Dec 2007 10:05 PM
 

Sam said:

Great app. One request, is to have a setting for the timeout on the bulk object. I am getting a timeout error in the middle of a large table. Thanks
17 Dec 2007 5:29 PM
 

fred.sobel said:

Thanks Sam: Added Timout and Batch Size. Please give it a try.
17 Dec 2007 10:36 PM
 

NLUser said:

great tool, helped me out on copying 200+ tables from sql 2k5 to 2k. Time-outs on large tables though. Perhaps add a partial transaction feature (like 50000 rows per commit)
18 Dec 2007 2:44 AM
 

NLUser said:

beaten to it :P great job
18 Dec 2007 2:45 AM
 

xs4all said:

Fred: thank you! Are the sources also available for download?
7 Jan 2008 3:10 PM
 

fred.sobel said:

The source is pretty rough right now so I did not make it available for download. The key to the code can be found here: http://davidhayden.com/blog/dave/archive/2006/01/13/2692.aspx
8 Jan 2008 12:32 PM
 

Billy McCafferty said:

I can't express how grateful I am for this. Thank you!!!!
13 Jan 2008 9:51 PM
 

KDSayers said:

I have to echo the sentiments of the others here. Thank you for putting in the time to do this. Not exactly happy with Microsoft's response on this issue. Thank you!!!
1 Feb 2008 3:45 PM
 

PaulG said:

You've saved me hours, works perfectly Thanks
5 Feb 2008 4:53 AM
 

CPP said:

Hey Fred, I have tried to download and run from the site but unsuccessfully. Really need your tool to work around SQL 2005, can you repost it on the web. That would be very appreciated. CPP
14 Feb 2008 10:52 AM
 

Fred said:

Ok, exe is here: http://projects.c3o.com/install/sqlcopy/SimpleSqlCopy.exe
14 Feb 2008 3:00 PM
 

CPP said:

Thank you very much !!!
14 Feb 2008 11:21 PM
 

Mikey said:

Turn off "optimise for multiple tables" and turn on "Enable identity Insert". That works for me! :)
18 Feb 2008 10:55 PM
 

Mark said:

I get an unhandled exception error when I try and connect to my 2005 database - it works when connecting to the 2000 database. Any help would be appreciated.
21 Feb 2008 5:18 PM
 

Tyler said:

I love you
1 Mar 2008 5:17 PM
 

TomP said:

Ran into the identity issue trying to copy database contents from a test to a new production 2005 server. I have the same problem as Mark, unhandled exception when connecting to a SQL 2005 as source. Even found the CONFIG.EXE and changed the source information there, and it still points to the original source (sqlexpress). Suggestions?
3 Mar 2008 4:40 PM
 

fred.sobel said:

TomP, Mark: Please try this version. http://projects.c3o.com/install/sqlcopy/SimpleSqlCopy.exe I added some error handling on connect. Let me know what kind of error you are getting. You can always get me at fcsobel@yahoo.com
3 Mar 2008 10:39 PM
 

panicz said:

Great app!!! :) I've a request: could you add control for the collation? thank you!
4 Mar 2008 3:34 AM
 

Abellix said:

Great!!!! It's midnight and finally I found the way to do the old work in a smart way! Thanks you I can go sleep! :D Just one suggestion: could you insert a checkbox to skip views from the list? Thanks again :D
7 Mar 2008 5:17 PM
 

TamerY said:

thanks for putting the efforts to put this together. But I get a message saying "Cannot access destination table '[dbo].[MyTableHere]'" Is there a special permissions for bulk copy? I'm using my built-in admin account, I also tried with sa account and still does not let go through... or is there something bad in my table naming? thanks.
10 Mar 2008 10:17 AM
 

fred.sobel said:

Are you sure the destination table is created? The tool copies data. It's up to you to create the tables but that part is easy using "Generate Script...".
10 Mar 2008 10:47 PM
 

TamerY said:

good point, I thought the too does create the original table with all keys and indexes then it copies the data, but I created it manually and it worked, thanks~!
13 Mar 2008 12:38 PM
 

Mike said:

how to donate to the author? =)
19 Mar 2008 10:34 AM
 

Rod Early said:

Great Tool!
4 Apr 2008 6:40 AM
 

word said:

How input the username/password. I am working remotely and do not have console access. It might have to do with the "Integrated Security=True" how do i input username/password. Thanks
8 Apr 2008 4:17 PM
 

word said:

I found it. Persist Security Info=True;User ID=sa;Password=password" added that instead of Integrated Security. Thanks sooo much.
8 Apr 2008 4:52 PM
 

sprint said:

Nice, Simple App! Thanks! Would be nice to have support for case-sensitive databases and have a checkbox for excluding views.
14 Apr 2008 9:02 PM
 

tamcdee said:

Great, thanks for sharing
22 Apr 2008 6:35 AM
 

Ulrich said:

When I try to copy data I get this error for 8-10 tables: The locale id '1030' of the source column 'alias' and the locale id '1033' of the destination column 'alias' do not match.
24 Apr 2008 6:29 PM
 

fred.sobel said:

Looks like the SQLBulkCopy class does not like copying data between different locale ids. (1033=US, 1030=Danish).
25 Apr 2008 10:27 PM
 

Sam said:

OMG finally I can export data!! Thanks for doing what microsoft couldn't. You're a legend!!
28 Apr 2008 10:38 PM
 

Mat said:

Great App. Thanks so much. I spend hours banging my head against the wall with the SQL Server data import wizard. One this though. I don't think the constraints are correctly enabled after the data load. You might need to use: exec sp_MSforeachtable 'ALTER TABLE ? WITH CHECK CHECK CONSTRAINT ALL' see http://www.eggheadcafe.com/software/aspnet/31300619/disabling-and-enabling-al.aspx Thanks again
2 May 2008 4:11 AM
 

fred.sobel said:

Thanks Mat, Added "with check" and edit window so you can modify the pre and post sql.
3 May 2008 8:15 PM
 

Gary said:

Thanks very much for sharing this. Works great.
3 Jun 2008 5:44 AM
 

Geoff said:

Thank you, thank you, thank you.
4 Jun 2008 12:03 PM
 

Chitrank said:

This is lovely , you have been very helpful.. Thanks loadsssssssss....
22 Jun 2008 8:10 AM
 

Badtz said:

Thanks God I found you
3 Jul 2008 2:20 AM
 

The Roach said:

This is awesome. Thanks a ton. Is there a way I can view the sql script that is executed that performs the copy.
17 Jul 2008 10:10 AM
 

fred.sobel said:

Roach; the copy is handled internally by the SqlBulkCopy class. You could use Profiler to see what gets passed to Sql Server.
21 Jul 2008 1:04 PM
 

User said:

try to copy data to another database in the same remote server but cannot error is - Cannot access destination table '[dbo].[name_list]'. Please help
30 Jul 2008 10:49 PM
 

fred said:

Are you sure the destination table is created? The tool copies data. It's up to you to create the tables but that part is easy using "Generate Script...".
31 Jul 2008 10:32 PM
 

Trevor said:

Thanks a pile!! I was dreading going through the list of tables (again) and enabling identity insert on every one. We don't have backend access and can't just take a simple backup You're a legend.
4 Sep 2008 8:09 AM
 

Josh Stodola said:

Thanks! Source code, please?
9 Sep 2008 10:16 AM
 

fred.sobel said:

http://sqlcopy.googlecode.com/svn/trunk/
10 Sep 2008 10:01 PM
 

Bugmeister said:

Seems to have a problem with calculated columns (i.e. needs to exclude from the copy I guess). I.e. threw up the following error: The column "FullQuotationCode" cannot be modified because it is either a computed column or is the result of a UNION operator.
30 Sep 2008 9:08 PM
 

Chas said:

Thank you!! You have no idea how many hours I spent wrestling with the DTSWizard before finding the page. A model of a program - simple, effective and it just plain works.
11 Dec 2008 4:10 AM
 

PockyMaster said:

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. If you want the updated code, let me know.
20 Jan 2009 3:09 PM
 

Geoff said:

I'm trying to move between two SQL Server 2005 instances. I keep getting errors if the source table has a datetime field. It keeps saying it is out of range, even though it most definitely isn't. Any ideas?
26 Jan 2009 12:12 PM
 

Travis said:

You should implement the an option to create or drop destination tables. Create would create the destination tables if they dont exist based on the source, and drop would drop the destination tables and re-create them before copying the data.
3 Feb 2009 1:23 PM
 

Travis said:

Almost forgot, thanx for the great tool!
3 Feb 2009 1:24 PM
 

PockyMaster said:

create/drop destination tables would not be that easy. What about relationships, indexes etc? Maybe adding a mapping functionality would be cool though.
4 Feb 2009 10:14 AM
 

Maslikhin said:

I got error "Invalid object name 'information_schema.tables'". I guess this is collation (binary or case-sensitive) related error, because 'INFORMATION_SCHEMA.TABLES' must be in upper case.
5 Feb 2009 3:51 AM
 

Ahmed El-Morali said:

Nice tool, but facing problems having computed columns. Can you fix it?
14 Apr 2009 7:03 AM
 

fred.sobel said:

Ahmed, there is a branch based on PockyMaster's code which may fix your problem. You can pull it down here: http://sqlcopy.googlecode.com/svn/branches/2.0.0.0/
19 Apr 2009 3:41 PM
 

fred.sobel said:

Exe for the branch is here: http://projects.c3o.com/install/sqlcopy/simplesqlcopy2.exe
19 Apr 2009 4:00 PM
 

Joel said:

Thanks Fred for adding the new features.
30 Apr 2009 2:19 PM
 

Bret said:

Does the source datasource have to be MS SQL Server? Could it be used to copy data from MySQL to MS SQL Server ?
12 Jun 2009 4:59 PM
 

Fred said:

There is SQL Server specific sql used to read the list of tables. You could grab the source and change the SQL to work with MySql. The BulkCopy class should be able to handle mySql? Im doing more work with oracle lately. If I have time I can try Oracle to Sql Server or MySql to Sql Server...etc.
12 Jun 2009 11:12 PM

Add Comment

Add
Name
Web Site
Comment
Powered by Community Server, by Telligent Systems