Exporting partial table records as INSERT script

by Dana   Last Updated February 13, 2018 14:06 PM

I have datatable with a big amount of data in it and I'm trying to export the data from it. I saved the file locally but I cannot open it with Notepad++. The problem is that I just want to get some of the data as an INSERT script but I don't know if this is possible.

Can anyone tell me if it's possible to get some of the data from a table as an INSERT script using SQL Server 2014?

Answers 1

Here is one way to do what you want (taken from this post:

  • Do a select [whatever you need] INTO temp.table_name from [... etc ...].
  • Right-click on the database in the Object Explorer => Tasks => Generate Scripts
  • Select temp.table_name in the "Choose Objects" screen, click Next.
  • In the "Specify how scripts should be saved" screen:
  • Click Advanced, find the "Types of data to Script" property, select "Data only", close the advanced properties.
  • Select "Save to new query window" (unless you have thousands of records).
  • Click Next, wait for the job to complete, observe the resulting INSERT statements appear in a new query window.
  • Use Find & Replace to change all [temp.table_name] to [your_table_name].
  • drop table [temp.table_name].

NOTE: The above sample uses the schema name temp. You may have to create that schema name for the script to work for you.

Scott Hodgin
Scott Hodgin
February 13, 2018 13:39 PM

Related Questions

SQL Server purchasing options in AWS

Updated June 05, 2017 13:06 PM

SQL Server Performance Issues & Reporting Requirements

Updated October 18, 2017 11:06 AM

SSISDB Catalog in sql server

Updated June 03, 2016 08:02 AM

Visual Studio SSIS Package throws error

Updated September 18, 2017 13:06 PM