Sign in
 
HomeLibraryLearnDownloadsTroubleshootingCommunityForums
SQL Server Developer Center >
> 
> 
Creating a reporting DB (MySQL to MSSQL)

Answered Creating a reporting DB (MySQL to MSSQL)

  • Thursday, January 07, 2010 3:38 PM
     
     
    Sign In to Vote
    0

    Hello,

    Im fairly new to SQL Server and could use some suggestions.  Im creating a new reporting database for one of our vendor supplied applications.  The application sits on MySQL and I am in the process of creating a MSSQL database for reporting purposes.  Ive worked through the data conversion issues and am now working on the process for updating the database.  The databases reside on two seperate servers (Linux to Windows). 

    Ive created a linked server and written some test SQL statements to move the data using the EXCEPT command.  Which is very handy but Im not sure it will work for this situation.  The EXCEPT command returns rows that do not exist in the new reporting table, but also returns existing records that have been modified since the last load.  The latter causes my insert statements to fail due to table constraints.  And the other obvious concern is how the SQL statements would affect system performance running across platforms.

    So at the moment Im thinking that truncating the data and reloading may be the better solution.  But I would assume this will become an issue as well when the database increases in size.

    Anyway, if anyone has any suggestions I would be glad to hear them, thanks ahead of time.

    • Moved by Tom PhillipsModerator Thursday, January 07, 2010 3:57 PM Possibly better answer from TSQL forum (From:SQL Server Database Engine)
    • Edited by sumnerdu Thursday, January 07, 2010 4:23 PM Title Change
    •  
     

All Replies