locked
Generating Insert Script using Select statement RRS feed

  • Question

  • I was wondering if there was a way that I could generate an insert script based on only certain rows returned from a select statement. I know how to script all data from a table into a sql file of inserts however if possible I would like to only script inserts for rows that meet certain search criteria:

    ie. SELECT *

      FROM [dbo].[Menu]

      WHERE MasterMenuID = 'de869900-4a61-4a32-9b0d-6ea5a9c9eb11'

    and generate those insert statements to be moved into an identical table in a currently empty database copy.

    Thanks in advance for any suggestions!

    Tuesday, January 17, 2012 6:11 PM

Answers

  • Wonderpanda,

    As Prakash suggested like below

     

     

    SELECT * INTO <NewTable> FROM [dbo].[Menu] WHERE MasterMenuID = 'de869900-4a61-4a32-9b0d-6ea5a9c9eb11'
    

     

    After executing the above statement your <NewTable> is ready ! okay ?

    Now you can generate the  script (Insert table) for the <NewTable> and take it on any of your server ( as you said identical table in a currently empty database copy)

     

    In your destination database , say you are having identical table i.e. mynewtable then you have to do as follows

     

    Insert into mynewtable select * from <newtable>
    


     

     


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
    • Edited by Manish_Kaushik Tuesday, January 17, 2012 6:52 PM
    • Marked as answer by WonderPanda Tuesday, January 17, 2012 6:59 PM
    Tuesday, January 17, 2012 6:48 PM

All replies

  • Tuesday, January 17, 2012 6:20 PM
  • If you just want to copy the data you may try something like this,

    SELECT * INTO <NewTable>

    FROM [dbo].[Menu]

    WHERE MasterMenuID = 'de869900-4a61-4a32-9b0d-6ea5a9c9eb11'

    OR

    INSERT INTO <NewTable>

    SELECT *

    FROM [dbo].[Menu]

    WHERE MasterMenuID = 'de869900-4a61-4a32-9b0d-6ea5a9c9eb11'

     


    Prakash Machiraju
    Please vote if you find this posting was helpful or Mark it as answered.
    • Proposed as answer by Manish_Kaushik Tuesday, January 17, 2012 6:24 PM
    • Unproposed as answer by WonderPanda Tuesday, January 17, 2012 6:39 PM
    Tuesday, January 17, 2012 6:20 PM
  • In my searches online I already found the stored procedure sp_generate_inserts and for some reason the insert statement stops after the first column value to be inserted and I have been unable to get it to complete the rest of the row information.

    As far as simply copying the data I don't think that will work for what I am trying to accomplish. I would like to be able to have the information saved as insert statements as they will be put into a script to be executed on newly deployed databases that interface with this application to ensure that default data is included upon deployment.

    Tuesday, January 17, 2012 6:39 PM
  • Wonderpanda,

    As Prakash suggested like below

     

     

    SELECT * INTO <NewTable> FROM [dbo].[Menu] WHERE MasterMenuID = 'de869900-4a61-4a32-9b0d-6ea5a9c9eb11'
    

     

    After executing the above statement your <NewTable> is ready ! okay ?

    Now you can generate the  script (Insert table) for the <NewTable> and take it on any of your server ( as you said identical table in a currently empty database copy)

     

    In your destination database , say you are having identical table i.e. mynewtable then you have to do as follows

     

    Insert into mynewtable select * from <newtable>
    


     

     


    Thanks
    Manish

    Please use Mark as Answer if my post solved your problem and use Vote As Helpful if a post was useful.
    • Edited by Manish_Kaushik Tuesday, January 17, 2012 6:52 PM
    • Marked as answer by WonderPanda Tuesday, January 17, 2012 6:59 PM
    Tuesday, January 17, 2012 6:48 PM
  • Thank you! Sorry for missing the obvious there it works fine like that.
    Tuesday, January 17, 2012 6:59 PM
  • Yes we can do this to make it possible i made i store procedure which works perfectly for any situations :

    visit this link for store procedure:

    http://sql-server-script.blogspot.in/

    Wednesday, October 24, 2012 12:43 PM