What is the best way to auto-generate INSERT statements for a SQL Server table?

by

Microsoft should advertise this functionality of SSMS 2008. The feature you are looking for is built into the Generate Script utility, but the functionality is turned off by default and must be enabled when scripting a table.

This is a quick run through to generate the INSERT statements for all of the data in your table, using no scripts or add-ins to SQL Management Studio 2008:

  1. Right-click on the database and go to Tasks > Generate Scripts.
  2. Select the tables (or objects) that you want to generate the script against.
  3. Go to Set scripting options tab and click on the Advanced button.
  4. In the General category, go to Type of data to script
  5. There are 3 options: Schema Only, Data Only, and Schema and Data. Select the appropriate option and click on OK.
    SqlDataOptions

You will then get the CREATE TABLE statement and all of the INSERT statements for the data straight out of SSMS.

More Related Contents:

  1. What is the use of GO in SQL Server Management Studio & Transact SQL?
  2. How to see query history in SQL Server Management Studio
  3. How do you view ALL text from an ntext or nvarchar(max) in SSMS?
  4. sql server invalid object name – but tables are listed in SSMS tables list
  5. SSMS Results to Grid – CRLF not preserved in copy/paste – any better techniques?
  6. How do I view the full content of a text or varchar(MAX) column in SQL Server 2008 Management Studio?
  7. How can I automate the “generate scripts” task in SQL Server Management Studio 2008?
  8. Query times out when executed from web, but super-fast when executed from SSMS
  9. How do you specify a different port number in SQL Management Studio?
  10. Unique constraint on multiple columns
  11. Format SQL in SQL Server Management Studio
  12. Import / Export database with SQL Server Server Management Studio
  13. Removing the remembered login and password list in SQL Server Management Studio
  14. SQL Server (localdb)\v11.0 explained
  15. The request failed or the service did not respond in a timely fashion?
  16. FOR JSON PATH results in SSMS truncated to 2033 characters
  17. SQL Server 2008 can’t login with newly created user
  18. How can foreign key constraints be temporarily disabled using T-SQL?
  19. Does T-SQL have an aggregate function to concatenate strings? [duplicate]
  20. Add IIS 7 AppPool Identities as SQL Server Logons
  21. How do I format a number with commas in T-SQL?
  22. SQL Server: Database stuck in “Restoring” state
  23. Access is denied when attaching a database
  24. Getting an odd error, SQL Server query using `WITH` clause
  25. Split one column into multiple rows
  26. SQL Unique constraint across multiple tables
  27. Changing SQL Server collation to case insensitive from case sensitive?
  28. How to store directory / hierarchy / tree structure in the database?
  29. Which is quicker COALESCE OR ISNULL?
  30. Merge multiple rows into a single row

Leave a Comment