Leron Leron - 1 year ago 52
SQL Question

Written transfer script for DataBase data and File copying

I know this is a bit general question for the criteria of

but I've read a couple of articles on similar topics and everybody says "This is a scratch work" but the more I think of a way to do it the more confused I get. I need to find some solution, at the end it may be any solution but if it is indeed really something standard and simple I want to get at least the basic steps I should follow to do this or even better - a tutorial or something like this that covers subject similar to mine.

What I have - an inherited database with 3 tables where there is a column
. At the old database this column is a
field which holds the whole path to the file source e.g
. There is also the new database where the same column
also exists but there it holds only the name of the picture. The paths are in a
table but I think this is not that important since the paths will be hard coded in the transfer script (or I think this is the way to be done).

So between the old and the new database this transfer script takes place and I need this logical steps :

  1. Take the value from the

  2. Check if there is existing file (something like
    I would imagine)

  3. Change the name of the Image File (There is new convention for naming so all names must be changed accordingly)

  4. Copy the image file to the destination folder (Path is hard coded in the script or taken from the table

  5. Copy the new Image File name at the new database column

  6. Do this for all the rows in a table and for all tables that have
    column (again hard coded since there are only 3 tables)

  7. Some kind of report for the unsuccessfully copied Files and detail information (table, row, file name - I'm not sure what would be best to show here)

I've read a lot about
but so far I think this is not what I should be looking for to solve this problem. I have some tricky parts as - it's not necessary to be an existing image associated with the path taken from the old database so if this is the case I must proceed but also log that info. Also, even not that possible to happen, during execution and more exactly, during copying the Image File to the new location again something may cause an error I guess I should only log the error but not very sure since there is an actual image.

However - what approach do you suggest to make this working? What to use and is there something designed exactly for such kind of situations that I should know of. The only thing I know right now is that I'm gonna write it in
. It will be most probably
Command Line
application and probably will need to be run as
which I'm not sure if this alone gonna make any difficulties.


I'm not sure if this is important for the current situation but the new application is build using
ADO.NET Entity Framework
code first approach. The developing server is
MS SQL Server 2012
but the working server will be with
MS SQL Server 2008
. I'm not sure if that gonna be an issue too, since now we didn't have any compatability problems except different collations but this was fixed.

Answer Source

I will prefer to dived the solution.

Designing an Intermediate table, something called PictureRepository table, this table will have all columns you need to read data from sources and all columns neede to save on target table and extra columns like status, error description and ...

  1. Reading Data (fill PictureRepository by reading source tables).
  2. Validating Data (verifying picture existence and ... by reading PictureRepository. updating stats and error description columns if required)
  3. Operation (creating new image file name and updating the column in PictureRepository)
  4. Saving data (reading no error data rows from PictureRepository and saving them to target table)

Reporting will be done by reading error data from PictureRepository.
you can do the phases in C# or SQL based on your problem complexity and your experience (reading data and saving data could be done by stored procedures).

Hope be helful