I've been using this code the last 5 years. I converted it to VB.NET because at the time I didn't know C# very well. I used it as the core of a record copier utility that copies very complicated driving records from one environment/database (e.g., DEV) to another (QA). It was a big productivity booster in our shop. Developers used it to copy driver records from Prod, where there was an issue in Prod, to Dev, for further diagnosis and recreating the issue. Testers used it to put baselined data records into a test database.
All thanks to this piece of code you shared.
So thanks again!
I have modified your code to detect exceptions of primary key on existing records on destination table. Now, the function create to cmd's. One for insert (yours) and other for update (me).
There is also the option to pass to the function a BackGroundWorker if somebody wants to inform the user the progress of the inserts / updates.
Finally, I have took care in the case that you are updating a table with only one field. If the copy fails, the code has only to ignore
(Update Table set ***** where field1=@field1 --> this case has to be ignored on updating)
(update table set field1=@field1 where ***** --> this too)
I have a problem where the cmdInsert returns a "column 'x' cannot be null" exception on every row. The problem is, I don't think x is null. When I step through the code the parameter @x is added and set with a integer value. I look through the debugger and everything has values but when that insertCmd executes it tosses the above exception.
I hadn't changed any of the code from what you had, I found the issue though in case any one else experiences it. I was using your code in this instance with the MySql ADO.Net provider. The variable names on the parameterized statement the MySql provider expects won't work with the "@" symbol. Instead it requires a "?". I believe there are other providers that behave this way (SQL Server's default is "@"). Once I changed it to a ? the load proceded fine (and it explains why when I manually checked, all of the values were in place).
I created a Vb.Net class based off of your code for a friend of mine to use. Instead of it being a static procedure I created a class with a few properties. I liked your code for it's simplicity that I figured I'd add my own bells and whistles to it:
1.) The ability to select the variable symbol to use (the default is "@", the property can then be changed)
2.) IsDestinationSqlServer property to know whether to do the SQL Server specific date checks you used
3.) UseTransaction property. If set to true will use an IDBTransaction for the load. Loading a 26 record test table it sped up the load time by about 40% (and, it doesn't commit the records if there's an error).
4.) DeleteAction property. Whether or not to delete the destination table's data before hand. It can be set to Delete, Truncate or NoAction (NoAction being the default).
5.) ElapsedTime property. Shows the time it took to run the last CopyTable sub.
If you want, I can post a copy of what I translated/added to. Let me know. Great work, this is a simple/useful way to move data, especially between different database engines.
all the best my friend you saved my lot of days work. one thing i want to ask you about how can i use a progress bar control to show the progress of database tables being copyied. i appreciate for all your help and good luck with your future..
Using a progress bar should be straight forward...
You would have to count the records in the table first using a "select count" sql statement. Then you could set the maximum value on the progress bar. Then each time you copy a record you could update the progress bar.
However I have a problem. I need to copy a table in Access which has an Autonumber column as the primary key. How can I copy the data without the ID of the data changing? When I try and run the code, it generates the following SQL:
No it's Access to Access. It's part of the installation routine for the product. So what it's actually doing is copying the existing user's data into the new product database (their favourites etc).
If it was SQL Server it would be easy because I could just enable IDENTITY INSERT inside a stored procedure. Access doesn't seem to have this, so I'm going to have to remove all the relationships, remove the autonumber primary key, copy the data and then recreate the autonumber and relationships.
Actually it turns out that the autonumber isn't the problem after all. I still get the syntax error even if I change the column to be a normal number column.
Perhaps the problem is that the code always assumes all of the values are strings (param.DbType is always String even if the source column was an integer. Not sure if this would cause a syntax error or not.
If I copy and paste the insert statement into access, and manually enter values instead of using the parameters, it seems to work fine. So the syntax error must be caused by properties of the insert statement perhaps?
OK, the syntax error was a small bug in your code. It won't copy a table if the column is called "ID" because ID seems to be a keyword. I've modified the code to wrap all column names in square brackets.
hmmm...I though access would do an implicit conversion in that case.
May need to write some conditional code in the method to detect the datatypes and set the parameters appropriately. Usually the code works fine if your source and destination tables have the same schema.
You probably CAN remove the bit that sets the DbType.
The important fix is the bit that wraps all the column names in square brackets.
INSERT INTO tblTable (ID, Column2)
INSERT INTO tblTable ([ID], [Column2])
..as otherwise, if any of your columns is a keyword or contains a space(!) then it won't work. I think you should modify your article to include at least this fix as it will make it much more reliable and the resulting error message ("Syntax Error") isn't very helpful. It could save people a lot of time and doesn't have any side-effects.
As you say, I think Access DOES do the implicit datatype conversion, but I didn't see any harm in leaving my modification in there (even though it didn't fix my original problem).
Agreed - spaces in database columns are a stupid idea! However I don't always design the databases I have to work with and even some column names without spaces can cause problems - such as if the column name is a reserved word like ID, primaryKey, index etc
Again, thanks very much for sharing the code - it's saved me hours of time!