Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2004
    Posts
    51

    Unanswered: How to export tables from the database?

    A very simple question
    I have two tables in the database on one server
    I want to export it to another database on another server
    How can I do this?
    I tried DTS but I guess it works from one server to another server on same network
    When I went to Query Analyzer, I can see scripting options but that only gives me the structure of the table in .sql format
    Is it possible to get the whole data in .sql format and then run it on another server
    Please suggest methods
    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    Burbank & Santa Cruz de la Sierra
    Posts
    3,716
    Have you tried going into Enterprise Manager and going to the "tables" section, then right-click on the table you want to export, and choose "all tasks" and then "export data" You can set up export options such as choosing any tables you want, and setting any conversion options.

    Not sure if this will work for you either though, since you mention your servers are not on the same network. Probably this option has pretty much the same underpinnings as the DTS option.

    This export option also, however, allows you to export the table/data to a flat file, so theoretically you could do that, then transfer it to the other server manually (i.e., email attachment or CD) then import it from the flat file to the database. I've never done the latter myself, but we have created CSV files from our data tables and handed them off to a different department that then (according to what we have been told) imports it into their database tables.

    BTW...what is the equivalent of Kryptonite in the programming world?
    Last edited by TallCowboy0614; 10-17-07 at 20:06.
    aka "Paul"
    Non est ei similis.

    I just read "100 Things To Do Before You Die". I was surprised that "Yell for help!!" wasn't one of them

  3. #3
    Join Date
    Nov 2004
    Posts
    51
    I have done this long time
    The only reason I asked here so that I get to know the best way to do it without any hassles
    <<<
    Have you tried going into Enterprise Manager and going to the "tables" section, then right-click on the table you want to export, and choose "all tasks" and then "export data" You can set up export options such as choosing any tables you want,>>>
    I reached here and selected .xls and got a .xls file
    I guess I should be able to import it in another server

    PS: what is the equivalent of Kryptonite in the programming world? Sorry?

  4. #4
    Join Date
    Nov 2004
    Posts
    51
    I got the .xls file
    When I try to import, it makes the Results or Results$ table
    When I renamed the sheet name to product, it created a table product$ rather than copying the data to the product table
    What should be done?

  5. #5
    Join Date
    Nov 2004
    Location
    on the wrong server
    Posts
    8,835
    Provided Answers: 6
    in the dts wizard you can specify the name of a destination table.
    If one brings so much courage to this world the world has to kill them or break them, so of course it kills them. The world breaks every one and afterward many are strong at the broken places. But those that will not break it kills. It kills the very good and the very gentle and the very brave impartially. If you are none of these you can be sure it will kill you too but there will be no special hurry. Earnest Hemingway, A Farewell To Arms.

  6. #6
    Join Date
    Oct 2007
    Posts
    1

    Scripting the schema and tha data of the table together

    xSQL Data Compare by xSQL Software (http://www.xsqlsoftware.com) allows you to right click on the table and script schema and data together. You can simply execute that script to the destination database and you have transferred the table.

    Furthermore, if you have a lot of rows on that table it allows you to choose to use "bulk insert" instead of generating individual insert statements for every row.

    It also allows you to script the whole database (the schema, the data or both schema and data together).

    Best of all the scripting functionality on this product is free - no activation or registration is required.

    Hope this helps.

    Thanks,
    JC
    xSQL Software
    http://www.xsqlsoftware.com

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    The two main ways of transferring data from one server to another remain DTS and BCP. DTS can transfer data from server to server, and is what the Export Data wizard uses in the background.

    BCP is a command line tool that can extract data from a table to a flat file, and put it back to a server. This works much better, when you have two servers that can not talk to each other directly. It is also useful to know BCP,since BCP has not gone through any major changes from version 2000 to 2005.

Posting Permissions

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