locked
Best way of copying tables RRS feed

  • Question

  • Hi all, 

    Have a database with over 100 tables in it that I need to move over to an almost identical database on a semi-periodic basis.  I say identical, however

    the index and PKs are different on the source & target databases.  

     

    I'm planning to truncate all the target tables then move the data from the source to target, need to count the rows in the source and target to ensure the cardinality is the same. 

    Looking for the best transformation to do this and wondered if anyone had any suggestions.  

    I know I can use the standard data flow task & row count transforms but with 100+ tables this is going to be quite lengthy to setup. 

     

    Regards

    Dave 

     

     

    Friday, May 7, 2010 2:07 PM

Answers

  • Hi Dave,

    Assuming all tables in your destination database are all setup and the column names are all the same as the origin database except index and pk.

    You could use a foreach loop in SSIS to loop through all the table names in the origin database and use a tsql component to run the following statement.

     

    delete from dest_db.table_variable

    insert into dest_db.table_variable
    select *
    from origin_db.table_variable

     

    you can loop and run insert statements to populate your tables. 

    The structure of the two databases will have to be same except index and pk.

     

    hope that helps.

     

     

    Jon

    Friday, May 7, 2010 3:27 PM

All replies

  • Hi Dave,

    Assuming all tables in your destination database are all setup and the column names are all the same as the origin database except index and pk.

    You could use a foreach loop in SSIS to loop through all the table names in the origin database and use a tsql component to run the following statement.

     

    delete from dest_db.table_variable

    insert into dest_db.table_variable
    select *
    from origin_db.table_variable

     

    you can loop and run insert statements to populate your tables. 

    The structure of the two databases will have to be same except index and pk.

     

    hope that helps.

     

     

    Jon

    Friday, May 7, 2010 3:27 PM
  • delete from dest_db.table_variable

    insert into dest_db.table_variable
    select *
    from origin_db.table_variable


    Hi, if your tables are big (i mean really big) delete statement will fragment index pages, just lookup fragmentation ratio and you may after decide to truncate and recreate index or to delete and rebuild index.

     


    BI and ERP Senior Consultant @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.

    Visit: ssisnedir.com/blog

    Friday, May 7, 2010 4:27 PM
  • Onur, how would you build that index dynamically when the index are different between the database?
    Friday, May 7, 2010 5:21 PM
  • I hope Jon's approach is best in this case. creating a package (or going with import/export  wizard) with 100 source and destination is not good idea..

     


    Let us TRY this |

    http://quest4gen.blogspot.com/
    Friday, May 7, 2010 6:01 PM
  • Onur, how would you build that index dynamically when the index are different between the database?


    I should have missed that paragraph :)

    Sorry Jon :)


    BI and ERP Senior Consultant @ Nexum Bogazici
    If it is, Please dont forget to mark as answered or at least vote as helpful if the post helps you in any ways.

    Visit: ssisnedir.com/blog

    Friday, May 7, 2010 6:14 PM
  • Thanks for the info, that all makes sense. 

    Never used a foreach loop container to loop through tables but I'm assuning that I could

    use the variable to truncate the destination table instead. 

    Do people think that an "insert into... select * from" would be quicker than the package

    objects that import wizard / save package comes up with? 

    Regards

    Dave

     

    Tuesday, May 11, 2010 10:39 AM
  • If there is no transformations involved between export and import, then don't go for ssis packages.

    Either BCP with format file or a blind insert will work. My suggestion here is BCP because I can do configurations and error checks there when compared to a blind insert which was discussed above.

     


    http://effulgentlogs.blogspot.com
    Tuesday, May 11, 2010 11:08 AM