Export SQL table from one server to another

  • Does anyone know where I can find information or tell me how to export a SQL table from a DB on server 1 to another server? All my searches have showed me export to excel. Any help will be appreciated

    Example:

    I need to export a table from Database A on SQLServer1 to Database B on SQLServer2

  • Several options exist.

    First, do you have the table structure already on the target server? Are you just trying to copy the data?

    Knowing the scope of this table copy will prove useful in giving answers more appropriate to your needs.

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • bcp.exe OUT can export a table to a csv file, and then bcp.exe IN can import that file into the other server. you can use this method if the servers cannot see each otehr over a network...you could FTP a file, or use the sneakernet and hand carry a file on a flash drive.

    SSMS has a built in import/export wizard in SQL server Management studio, if the machine you are using can see both servers (ie on the same network, or with a VPN)

    if there's not too much data, a linked server may be another possibility, which also assumes the servers can see each other.

    Lowell


    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • Might as well plug the tools from RedGate as well. SQL Compare and SQL Data Compare.

    One more option would be via RedGate Virtual Restore (where you could just restore the table to a new database).

    Jason...AKA CirqueDeSQLeil
    _______________________________________________
    I have given a name to my pain...MCM SQL Server, MVP
    SQL RNNR
    Posting Performance Based Questions - Gail Shaw[/url]
    Learn Extended Events

  • Perhaps you could use SSIS but it requires at least one sql server standard edition.

    Another option is Linked server but i think it requires at least one sql server standard edition also.

  • SSMS has generate script wizard which exports all schema scripts and insert TSQL statements (if Script Data is true). The same/similar wizard (Database Publsihing Wizard) is available in Server Explorer in Visual Studio (from Publish to provider... context menu).

  • Is it a linked server? If so, then why not a simple "select * into table b from table a" where table b is the four-part name? Alternatively, you could do the same thing with opensource or openquery, yes? For a q&d table copy....

  • Is it a linked server? If so, then why not a simple "select * into table b from table a" where table b is the four-part name? Alternatively, you could do the same thing with opensource or openquery, yes? For a q&d table copy....

Viewing 8 posts - 1 through 7 (of 7 total)

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