none
How do you reverse-engineer or "Script table as..." a table and include the data

    Question

  • I am using SQL Server 2008.

    I have a table with records in it.  I am using a testing database at this time, but do not want to reenter some of the data in the training database. When using another relational database I was able to basically reverse engineer the table and include the data.  Basically it would create a query to create the existing data with the fields and indexess and then create another query to insert the existing records into the table. It was a basic Insert into "table name" values (1,2,3,4).
    How can this be done in SQL server.  I tried the "Script Table As... Insert to"  and it created a script to insert data, but did not include the existing records.  How can this be done in SQL Server?
    Monday, May 18, 2009 5:22 PM

Answers

  • Here is one method:
    http://vyaskn.tripod.com/code.htm#inserts

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    Monday, May 18, 2009 7:54 PM
  • There is no built-in function in SSMS 2005 to generate insert
    statements. You can use the database publishing wizard:
    http://weblogs.asp.net/scottgu/archive/2006/12/22/recipe-deploying-a-sql-database-to-a-remote-hosting-environment-part-1.aspx
    http://www.codeplex.com/sqlhost
    http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    Monday, May 18, 2009 9:36 PM

All replies

  • Right click the database in SSMS object exporer, select Tasks, Generate
    Scripts. On the choose Script Options window of the wizard scroll down
    to Table/view Options and set Script Data to True. On the following
    screens select the tables to script.

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    Monday, May 18, 2009 5:27 PM
  • I also wondered how to do this so I checked it out and I didn't get a "Script Data" option. I am using SSMS 9.00.4035.00.

    Jacob
    Monday, May 18, 2009 6:57 PM
  • I followed the directions above and there was no option for Script Data.  Everything else was fine, this is all that was available:

    • Script Check Constraints
    • Script Foreign Keys
    • Script Full-Text Indexes
    • Script Primary Keys
    • Script Indexes
    • Script Triggers
    • Script Unique ID

    Any other place it would be?

    Monday, May 18, 2009 7:02 PM
  • This is available only on SQL Server 2008.

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    Monday, May 18, 2009 7:12 PM
  • As I noted it is on the Script Options window (which appears prior to
    the window to pick what to script), under Table/View Options.

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    Monday, May 18, 2009 7:14 PM
  • Oh I am sorry I have SQL Server 2005 - 9.00.3042.00

    Is there any other options in 2005
    Monday, May 18, 2009 7:19 PM
  • Here is one method:
    http://vyaskn.tripod.com/code.htm#inserts

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    Monday, May 18, 2009 7:54 PM
  • Thanks for the link, is there an easier way instead of using the stored proc?  What about the import/export data wizard utility under the task menu
    Monday, May 18, 2009 8:42 PM
  • There is no built-in function in SSMS 2005 to generate insert
    statements. You can use the database publishing wizard:
    http://weblogs.asp.net/scottgu/archive/2006/12/22/recipe-deploying-a-sql-database-to-a-remote-hosting-environment-part-1.aspx
    http://www.codeplex.com/sqlhost
    http://www.codeplex.com/sqlhost/Wiki/View.aspx?title=Database%20Publishing%20Wizard

    --
    Plamen Ratchev
    http://www.SQLStudio.com
    Monday, May 18, 2009 9:36 PM
  • Thanks for the link, is there an easier way instead of using the stored proc?  What about the import/export data wizard utility under the task menu

    Why?

    I think that sp is awesome. I always figured somehting like that could be done but I never took the time to do it.

    Jacob
    Monday, May 18, 2009 10:41 PM
  • I agree its a great SP, but I was looking for something through the client, basically a point and click kind of thing that's all.  Like what was suggest to use earlier in the blog, but it is only available on SQL Server 2008
    Tuesday, May 19, 2009 1:53 PM
  • I agree its a great SP, but I was looking for something through the client, basically a point and click kind of thing that's all.  Like what was suggest to use earlier in the blog, but it is only available on SQL Server 2008
    • Copy the sp into SSMS
    • remove everything before the first BEGIN statement
    • add DECLARE and SET statements for each of the SP's parameters
    • Change all "RETURN #" statements to "SELECT # as ResultCode", where # is an integer literal
    • remove everything after the last END statement

    Then, save the script in an .SQL file.

    Now you can run the script from the client whenever you need it, without having to actually make an SP. It isn't as convenient as the GUI for 2008 but those of us stuck with 2005 for the moment are just going to have to live with it.

    Jacob

     

    Friday, May 22, 2009 4:23 PM