Populate Database Tables with data from another Database



    I have two Databases with the same name that reside in different servers. The two servers are linked servers. Each database consists of 5 Tables. The tables have identical names in each database.


    The database in ServerA has 5 tables with data, the database in ServerB also has 5 tables with the same schema as ServerA, however the 5 identical tables in ServerB contain no data. 


    I need to populate the 5 tables in ServerB with the Data from the 5 tables in ServerA.


    What SQL code or script can i use to populate the 5 tables in ServerB


    Below are the names of the 5 tables:


    TABLE1:               [ServerB].[ProdDB].dbo.[Orders]

    TABLE2:               [ServerB].[ProdDB].dbo.[Sales]

    TABLE3:               [ServerB].[ProdDB].dbo.[Employee]

    TABLE4:               [ServerB].[ProdDB].dbo.[Customer]

    TABLE5:               [ServerB].[ProdDB].dbo.[Region]



    Does any one have a script that could help me with this task. Thanks



    I am using SQL Server 2005
    Friday, May 02, 2008 2:57 AM

All replies

  • I think this would work:

    Code Snippet

    INSERT [ServerB].[ProdDB].dbo.[Orders]
    SELECT * FROM [ProdDB].dbo.[Orders]


    INSERT [ServerB].[ProdDB].dbo.[Sales]
    SELECT * FROM [ProdDB].dbo.[Sales]


    INSERT [ServerB].[ProdDB].dbo.[Employee]
    SELECT * FROM [ProdDB].dbo.[Employee]


    INSERT [ServerB].[ProdDB].dbo.[Customer]
    SELECT * FROM [ProdDB].dbo.[Customer]


    INSERT [ServerB].[ProdDB].dbo.[Region]
    SELECT * FROM [ProdDB].dbo.[Region]



    Friday, May 02, 2008 4:18 AM
  • Well, the "best" way to go is to build yourself a quick SSIS (or DTS in 2000) package to do this.  It will be WAY faster than anything you can do in a script with a linked server.  But if you have linked servers, yoiu can just write normal statements:


    insert into dbname.dbo.orders

    select <columns>

    from   serverB.prodDb.dbo.orders


    It will be a bit slow, but if it isn't much data it will be fine.  But definitely use SSIS if you can.  It is much cooler and you can see how long it will take, it will use bulk insert mode and go a lot faster.

    Friday, May 02, 2008 4:20 AM