Simple SQL Copy

Sql Server, Oracle & MySql

Simple Sql Copy allows you to copy data between like tables while keeping identity and null columns intact. Used in conjunction with the generate script wizard you can make a complete database copy.

Download Install Download Zip

The following prerequisites are required:

Microsoft .NET 4.5.1

July 06, 2012

I'm trying to use the tool but always get the message "Incorrect syntax near the keyword from". Does anyone have any ideas about what I'm doing wrong? ... Thanks

December 06, 2011

since the September version there is no "sql" row anymore for each table to copy which i can edit to remove certain columns that need no copy :(

October 20, 2011

Great work to keep it going. I am still using the 2010 version very frequently :)

May 20, 2011

Experienced the same issue with the column ordinate positions. Column orders are alphabetical when they should not be.

September 15, 2010

I have discovered an apparent glitch: From an error that I got copying the info of one specific table it seems that the copying is done assuming that the columns have the same order in both the source and destination tables, so the match is positional and not done by column name. Fortunately the column types were different, so it showed an error, but if the column types were the same than the contents of the columns would be switched on the destination, and the problem might go unnoticed.

September 15, 2010

What a very fine app you have here. If you ever wonder how to make it absolutely perfect, please consider adding the option of sorting by object name and filtering by object type (i.e. only tables). Great work!

fred.sobel May 09, 2010

Rick, it will take any valid connection string. I use sql auth all the time. The format is someting like this: data source=192.123.4.5,1234;Initial Catalog=my_db;User ID=login;pwd=password; Where 1234 is the port #.

fred.sobel May 09, 2010

Rick, it will take any valid connection string. I use sql auth all the time. The format is someting like this: data source=192.123.4.5,1234;Initial Catalog=my_db;User ID=login;pwd=password;

May 09, 2010

Hey Rick, it will take any valis connection string. I use sql auth all the time. The format is someting like this: data source=192.123.4.5,1234;Initial Catalog=my_db;User ID=login;pwd=password;

May 04, 2010

Hi Fred, how to configure the connection string if i going to use SQL authentication instead of windows. This is due to servers are place in different region.

March 05, 2010

Thanks for adding the column mapping. Just needed that :D

February 24, 2010

You ROCK!

October 29, 2009

VERY nice work guys!! Just saved our butts trying to move off a hosted DB with no "real" access to perform backups, etc.

September 15, 2009

Thanks you just saved my life.

June 12, 2009

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.

June 12, 2009

Does the source datasource have to be MS SQL Server? Could it be used to copy data from MySQL to MS SQL Server ?

MMALAFRONTE June 08, 2009

I am executing a BCP command from within a VB6 application. When the length of the Sql record I am copying out is increased to 131 characters the last character is not copied out. Whhen I set theh record length to 130 or less all is well. Is there some sort of setting I can change? Any help would be appreciated. Michael

April 30, 2009

Thanks Fred for adding the new features.

fred.sobel April 19, 2009

Exe for the branch is here: http://projects.c3o.com/install/sqlcopy/simplesqlcopy2.exe

fred.sobel April 19, 2009

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/

fred.sobel April 19, 2009

There is a branch based on PockyMaster's code which may your problem. You can pull it down here: http://sqlcopy.googlecode.com/svn/branches/2.0.0.0/

April 14, 2009

Nice tool, but facing problems having computed columns. Can you fix it?

February 05, 2009

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.

February 04, 2009

create/drop destination tables would not be that easy. What about relationships, indexes etc? Maybe adding a mapping functionality would be cool though.

mxmissile February 03, 2009

I have modified the source to include an option that creates destination tables automatically.  A couple caveats:

  1. It requires the Microsoft.SqlServer.Management.* namespace, which gets installed when you install the latest Sql Studio.
  2. Requires VS 2008, .NET 3.5 to compile.

Anyway, if you or anyone is interested in the source, let me know.

February 03, 2009

Almost forgot, thanx for the great tool!

February 03, 2009

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.

January 26, 2009

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?

January 20, 2009

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.

January 15, 2009

Eric, sounds like you have a whole new version. Send an email to fcsobel@yahoo.com and I'll set you up to admin the code.

January 15, 2009

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.

December 11, 2008

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.

wiredone October 17, 2008

i use this nearly every day and it is my favourite little utility/tool over subsonic

however i have one gripe...

there is no progress indicator; if you have a table that is huge you have no way of knowing if something is still happening of the thing has hung (which it never has but hey 'd like to know how long i have to wait - even if just a percentage

September 30, 2008

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.

alacom September 17, 2008

Hi,

Let me first start by saying thanks for providing such a helpful utility.

How does it handles uniqueidentifier data?

I tried to copy an aspnet_user membership table whose primary key column (userid) is a uniqueidentifier, along with tables that used that column (userid) as its foreign key reference, and those tables failed to copy the data.

Below was the error message

"The given value of type Int32 from the data source cannot be converted to type uniqueidentifier of the specified target column."

Thanks

September 10, 2008

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

fred.sobel September 10, 2008

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

September 09, 2008

Thanks! Source code, please?

September 04, 2008

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.

July 31, 2008

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...".

July 30, 2008

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

fred.sobel July 21, 2008

Roach; the copy is handled internally by the SqlBulkCopy class. You could use Profiler to see what gets passed to Sql Server.

fred.sobel July 17, 2008

The copy is done using the SqlBulkCopy class. I create a datareader using "select * from table", set destination, options and the call the WriteToServer method passing in the reader.

July 17, 2008

The copy is done by the SqlBulkCopy class. All I do is create a datareader using "select * from table", set destination, options and the call the WriteToServer method passing in the reader.

July 17, 2008

This is awesome. Thanks a ton. Is there a way I can view the sql script that is executed that performs the copy.

July 03, 2008

Thanks God I found you

June 22, 2008

This is lovely , you have been very helpful.. Thanks loadsssssssss....

June 04, 2008

Thank you, thank you, thank you.

June 03, 2008

Thanks very much for sharing this. Works great.

fred.sobel May 12, 2008

fred.sobel May 03, 2008

Thanks Mat, Added "with check" and edit window so you can modify the pre and post sql.

fred.sobel May 03, 2008

Thanks Mat, Added with check and edit window so you can modify the pre and post sql.

May 02, 2008

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

April 28, 2008

OMG finally I can export data!! Thanks for doing what microsoft couldn't. You're a legend!!

fred.sobel April 25, 2008

Looks like the SQLBulkCopy class does not like copying data between different locale ids. (1033=US, 1030=Danish).

April 25, 2008

Sounds like it does not like copying data between different locale ids. 1033 = US 1030 = Danish Can you set both source and destination to same locale?

April 24, 2008

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.

April 22, 2008

Great, thanks for sharing

April 14, 2008

Nice, Simple App! Thanks! Would be nice to have support for case-sensitive databases and have a checkbox for excluding views.

April 08, 2008

I found it. Persist Security Info=True;User ID=sa;Password=password" added that instead of Integrated Security. Thanks sooo much.

April 08, 2008

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

April 04, 2008

Great Tool!

March 19, 2008

Thanks Mike:) See the shiny new donate button at the top or just click on my name here.

March 19, 2008

Thanks Mike:) Added a shiny donate button at the top of the page or click on my name.

March 19, 2008

Thanks Mike:) I added a shiny paypal donate button at the top. or just click on my name.

March 19, 2008

how to donate to the author? =)

March 13, 2008

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~!

fred.sobel March 10, 2008

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...".

March 10, 2008

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..."

March 10, 2008

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.

March 07, 2008

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

March 04, 2008

Great app!!! :) I've a request: could you add control for the collation? thank you!

fred.sobel March 03, 2008

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

March 03, 2008

Please try this version. 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 http://projects.c3o.com/install/sqlcopy/SimpleSqlCopy.exe

March 03, 2008

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?

March 01, 2008

I love you

February 21, 2008

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.

February 18, 2008

Turn off "optimise for multiple tables" and turn on "Enable identity Insert". That works for me! :)

markworsnop February 17, 2008

This little program has saved the day!  thanks!  any chance I could get a copy of the source so I understand how this works?

February 14, 2008

Thank you very much !!!

February 14, 2008

Ok, exe is here: http://projects.c3o.com/install/sqlcopy/SimpleSqlCopy.exe

February 14, 2008

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

fred.sobel February 09, 2008

I've been testing a new version of simple bulk copy for a few weeks now and thought I would make it available for click once install.  So I published the project to my file system and uploaded the new version to the ftp site.  Fired up the app, pulled down the new version and wham ran into this nasty error: "Configuration system failed to initialize"!  Ouch.

Turns out I had decided to do some cleanup and change my namespace from Test.... to c3o.... which should be okay but this also changed the App.Config which causes all the configurations to be under

c3o.SqlCopy.Properties.Settings instead of Test.SqlCopy.Properties.Settings

So after some quick changes I reverted back to the old "Test" namespace and all should be well.  Let me know if you run into any problems!

 

 

February 05, 2008

You've saved me hours, works perfectly Thanks

February 01, 2008

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!!!

January 13, 2008

I can't express how grateful I am for this. Thank you!!!!

fred.sobel January 08, 2008

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

xs4all January 07, 2008

Fred: thank you! Are the sources also available for download?

Tarwn December 26, 2007

I am in the process of trying to copy data from approximately 10000 tables (don't ask) from server A to server B. Unfortunatly when you type in the connection string from the first database and click the "Refresh" button, the interface pulls in all of the Tables and Views. Would it be possible to have a checkbox to only display tables, or somethign of that nature?

Tarwn December 26, 2007

This is a result of me being impatient with the tool, but if you click the "Copy Data" button, then click it again before the copy has completed, you will receive a .Net exception (System.InvalidOperationException) from the BackgroundWorkerProcess.

Suggestions:
 - Disable the Copy button until the executed copy process is complete
 - Add some type of indicator to show that the application is in the process of a copy (a label or progress indicator somewhere perhaps)

December 18, 2007

beaten to it :P great job

December 18, 2007

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)

fred.sobel December 17, 2007

Thanks Sam: Added Timout and Batch Size. Please give it a try.

December 17, 2007

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

December 11, 2007

Excellent app fred.sobel. Neat and Simple. I'm sure a lot of DB Admins/Developers will find it very useful

fred.sobel December 04, 2007

Guci, Changed it to [table_schema].[table_name] using information_schema.tables

December 04, 2007

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!

fred.sobel December 03, 2007

Matt: You can pull the exe down from here: http://projects.c3o.com/install/sqlcopy/SimpleSqlCopy.exe

fred.sobel December 03, 2007

Guci: thanks, I'll give it a try.

December 03, 2007

Guci: thanks, I'll give it a try. Matt: You can pull the exe down from here: http://projects.c3o.com/install/sqlcopy/SimpleSqlCopy.exe http://projects.c3o.com/install/sqlcopy/SimpleSqlCopy.exe

December 03, 2007

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.

December 03, 2007

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.

November 29, 2007

Ah - was using firefox - file needs to be downloaded with IE only

November 29, 2007

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"

November 29, 2007

This program works!

fred.sobel November 25, 2007

Converted to a ClickOnce install to keep versions updated.

fred.sobel November 24, 2007

Changed checkbox list to DataGrid to display errors.  Also changed call to asynchronous for better feedback.  Added user settings to remember source and destination.

fred.sobel November 24, 2007

I'm making some minor enhancements.  Post your feedback here.

Features

  • Copy Progress
    Copy Progress
  • Keep Identity & Nulls
    Keep Identity & Nulls
  • Sql Server, Oracle & MySql
    Sql Server, Oracle & MySql
  • Edit Select SQL
    Edit Select SQL
  • Command line version
    Edit Select SQL