Move data between databases

  • I need to update a table in database A from database B with the same/similiar columns in the tables. Would this be better accomplished through DTS or just a simple script like:

    insert into table A (column1, column2, ...)

    select from (column1, column2, ...) from table B where ....

    Are there any advantages/disadvantages to using one over the other? I will be doing this just once...hopefully!

    Any help or assistance would be greatly appreciated!

  • DTS will append all the data, or truncate/reinsert all.

    I like the control of the insert statement.

  • Within the same server we can do everything with a query analyzer as well as DTS Package(transfer data between databases, append, truncate, delete check previous rows). Also original poster says it is most likely one time task.

    It would be easier to write SQL statements and execute. I will go with SQL if it is one time task.

    I would like know which is faster.

    Regards,
    gova

  • Would I have to connect to both databases in the script, say:

    Use dbo.databaseA

    insert into table A (column1, column2, ...)

    Use DatabaseB

    select from (column1, column2, ...) from table B where ....

    Or how would I go about doing that via the script option?

  • (from db1)

    Insert into [db1.]dbo.TableName (col1, col2)

    Select Col1, Col2 from db2.dbo.TableName2

  • DTS uses bulk copy. So, in most cases DTS is faster.


    Kindest Regards,

    Gimli

    If you are a spineless creature, they can't break you

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply