Post reply

Generate Insert scripts for existing data in table

  • May 29, 2019 at 10:23 pm

    #3648436

    SQL Server 2014

    Thought this may be useful to others so posting it in the fourm.

    I didnt find an option in sql server management studio to generate insert scripts for data from an existing table.

    So, Iam using the below script to generate insert statements for data from an existing table so that i can deploy it to a higher environment.

    Output of the below will be 3 statements for each table. Run the middle statement which will generate the insert scripts. Then replace the middle statement with the insert scripts. Now the DML is ready for deployment.

    You can run it for multiple tables simultaneously. In the below iam executing for 2 tables. (TABLE1 and TABLE2).

     

     

     

  • May 29, 2019 at 10:25 pm

    #3648440

    Attached is the script.

    Attachments:
    You must be logged in to view attached files.

  • May 29, 2019 at 10:32 pm

    #3648443

    sudheer.kovelamudi wrote:

    I didnt find an option in sql server management studio to generate insert scripts for data from an existing table.     

    There is an option, but it's well hidden.

    • Right click on the database (not the table) in Object Explorer, and select Tasks/Generate Scripts.
    • Click 'Select specific database objects' and select the table(s) you are interested in.
    • Click 'Advanced' and find 'Types of data to script'. Modify this option to 'Data only'

    The absence of evidence is not evidence of absence
    - Martin Rees
    The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
    - Phil Parkin

  • May 30, 2019 at 3:53 pm

    #3648646

    Thanks Phil. that was very helpful.

  • May 31, 2019 at 8:54 am

    #3648897

    See the Thread :

    https://www.sqlservercentral.com/forums/topic/sp_generate_inserts

    Sp_generate_inserts can still be found. It does generate inserts scripts and has a lot of options. It can be called from a script or a stored procedure. Some other suggestions are done in the thread for some alternate methods.

     

    Ben

     

Viewing 5 posts - 1 through 4 (of 4 total)

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