Post reply

Help with script to copy data from DB to DB within SQL Server

  • May 14, 2008 at 9:02 pm

    #186635

    Hello,

    I have data in one table and need to copy it over to another table. Except the table I need to transfer it to is in another SQL Server 2005 database (on the same server). I am familar with INSERT, UPDATE, and SELECTs, but I am not too sure about how tell SQL Server to USE a table in another database. Can this be done with T-SQL?

    Any advice would be much appreciated.

    Thank you,

    Paul

  • May 14, 2008 at 10:07 pm

    #815606

    Sorry, looks like this should have been posted in the SQL Server 2005 forum.

  • May 14, 2008 at 10:08 pm

    #815607

    Hi paul,

    Open Query Analyzer connected to the database where you want the data to transfer see below the example

    xyz is the table and databasename abc -- blank table

    xyz1 is the table and databasename abc1 -- table with data

    connect thu query analyzer to abc databse and write the following query

    insert into xyz ( a,b,c,d) select a,b,c,d from abc1.sa.xyz1

    sa is the user who has the rights for the particular table

    Regards

    Sreenivas:)

  • May 14, 2008 at 10:11 pm

    #815609

    If it is in different database but same server it can be done using the 3 part naming convention (DBName.DBOwner.Table) or (DBName..Table) if the owner is dbo only.

    example :

    INSERT INTO Table1

    select * from DBName2.dbowner.Table2

    Hope that's what your looking for 😀

    "-=Still Learning=-"

    Lester Policarpio

  • May 15, 2008 at 12:03 pm

    #816084

    Thanks, that help a lot!

    Paul

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

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