Home Forums SQL Server 2012 SQL 2012 - General Need to export one table to another database in a different server/db RE: Need to export one table to another database in a different server/db

  • Another way you can do it in SQL Server 2014:

    SSMS:

    Right click on the database, select Tasks, select Generate Scripts.

    Click 'Select specific database objects'.

    Expand the Tables tree and click the box of the table in question.

    Next.

    Click the Advanced button.

    You'll get a screen with lots of options. The critical ones are the last one of the General section that allows you to script out data in addition to just the schema, you'll want 'Schema and data'. In the Table/View Options section you want to turn on the options for Script Indexes, Script Primary Keys, Script Triggers (perhaps) and Script Unique Keys. You might want to turn off the Script USE DATABASE option.

    The output has the DDL for building the table along with all defaults, indexes, etc. And it also has the DML for inserting all of the data. You definitely should review the output before running it in another database.

    I do this all the time to make design changes to tables that already have data in them in SQL Server 2014, no guarantee that this will work with previous versions. I usually also have to use the 'Generate Script for Dependent Objects' option, it works really well when tuning a design during development.

    Of course, if it's a big table, I'd use other methods, but it works fine for 600ish row tables.

    -----
    [font="Arial"]Knowledge is of two kinds. We know a subject ourselves or we know where we can find information upon it. --Samuel Johnson[/font]