Results 1 to 5 of 5

Thread: Transfer tables

  1. #1
    Join Date
    Sep 2003
    Posts
    212

    Angry Unanswered: Transfer tables

    Transfer tables from one server to the other on a regular basis. I'm doin this using the DTS import wizard (saved it as a package and scheduled a job). But i noticed that if the table structure changes on the soruce tables, the DTS package will fail.

    I want to make a DTS package that imports 6 tables, now how do i make it such that if the structure of the tables changes it shud still work. ie, everytime the tables are tranferred, the old tables on the target server must be dropped and re-created with the source server table definition. I hope this is doable.

    Plz help if ne one has done this b4.

    Thx

  2. #2
    Join Date
    Nov 2002
    Location
    Jersey
    Posts
    10,322
    Get used to creating packages from scratch...create an EXECUTE SQL Taks that will do the DROP and another one that will do the CREATE
    Attached Thumbnails Attached Thumbnails dts.bmp  
    Last edited by Brett Kaiser; 06-16-04 at 14:53.
    Brett
    8-)

    It's a Great Day for America everybody!

    dbforums Yak CorralRadio 'Rita
    dbForums Member List
    I'm Good Once as I ever was

    The physical order of data in a database has no meaning.

  3. #3
    Join Date
    Sep 2003
    Posts
    212

    Angry

    but if the package runs regularly.. and one of the many times, the tables' being transferred structure changed. how the heck is the EXECUTE SQL Taks gonna know bout this?
    is there a way to dynamically get the table structure from the source server?!


    understand?

    thx

  4. #4
    Join Date
    Feb 2004
    Location
    Poland
    Posts
    96
    2 conections and 2 task :
    1.drop table
    2. select into

    the second :

    SELECT *
    INTO desttable
    from server.db.dbo.sourcetable

    should be OK

  5. #5
    Join Date
    Sep 2003
    Posts
    212

    Angry

    Quote Originally Posted by rafala
    SELECT *
    INTO desttable
    from server.db.dbo.sourcetable

    should be OK
    the source server must be a linked server for that matter rite?

    how bout gettin the info for the tables from the system tables of the source server?...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •