locked
How do you pull data or replicate a table from one server to another? RRS feed

  • Question

  • I'm new to MS Sql Sever Management Studio and need some help pulling data from a table in Server A and creating the same table on Server B.

    From what I've read you can't export/import or use a wizard as the db is of the type dbo.md_*****.

    Any help will be greatly appreciated.

    Charles

    Friday, August 21, 2015 3:58 PM

Answers

  • If you are using SSMS version 2012 or later, you can simply script the entire table.

    From the context menu for a database, choose "Tasks\Generate Scripts...".

    In the Generate and Publish Scripts wizard, Choose Objects to set tables to script. Then go to "Set Scripting Options" and click Advanced. Be sure to change "Types of data to script" to "Schema and data". This will generate all of the needed statements to create the table and insert the data as it is in the existing table.

    The rest of the wizard is pretty easy to navigate.


    Dan Randolph - My Code Samples List

    Friday, August 21, 2015 10:07 PM

All replies

  • Charles,

       Is this a one time thing you need to do or is this something that you need to replicate?

    If this is a one time "quick and dirty" type of thing.  There are many different ways to do it.

    USE YourDataBase
    GO
    ----Create Table
    CREATE TABLE MyTable(FirstName VARCHAR(100), LastName VARCHAR(100))
    ----INSERT INTO TestTable using SELECT
    INSERT INTO MyTable(FirstName, LastName)
    SELECT FirstName, LastName
    FROM Person.Name
    WHERE EmailPromotion = 2
    ----Verify that Data in MyTable
    SELECT FirstName, LastName
    FROM MyTable
     GO

    Or more simply, using INTO

    SELECT FirstName, LastName
    INTO MyTable
    FROM Person.Name
    WHERE EmailPromotion = 2

    If you are talking about replication, than that is a different story and more info would be needed.

    Friday, August 21, 2015 6:00 PM
  • Daniel,

    It's a one time thing.

    If I could send you some screenshots of the db's involved would that help?

    Thanks for your help,

    Charles Trent


    Friday, August 21, 2015 7:10 PM
  • If you are using SSMS version 2012 or later, you can simply script the entire table.

    From the context menu for a database, choose "Tasks\Generate Scripts...".

    In the Generate and Publish Scripts wizard, Choose Objects to set tables to script. Then go to "Set Scripting Options" and click Advanced. Be sure to change "Types of data to script" to "Schema and data". This will generate all of the needed statements to create the table and insert the data as it is in the existing table.

    The rest of the wizard is pretty easy to navigate.


    Dan Randolph - My Code Samples List

    Friday, August 21, 2015 10:07 PM