Exporting Data From SQL Server to Script File


To totally unlock this section you need to Log-in


Login

SQL Server provides the ability to generate scripts for creating databases, tables, stored procedures, functions, views and inserting data. For smaller amounts of data, this method can be used to send table structures and/or data to Scribe Support for testing purposes.

  • In SQL Server under Databases, select the appropriate database.
  • Right-click the database and select Tasks > Generate Scripts.

Exporting Data From SQL Server to Script File

NOTE: Depending on the version of SQL Server, the screens may not be the same or may be displayed in a different order. Refer to the next example (Example 2) for options from a different version of SQL Server.

  • Select the tables for which to generate the scripts.
  • Select Set Scripting Options and specify the location to save the files.
  • Click the Advanced button and specify that you want both the schema and data, otherwise only the schema file is generated.
  • Click through the rest of the options using the Next button.

Exporting Data From SQL Server to Script File

If there is a large amount of data another option is to use a SQL Insert statement to create a new table and limit the data via a Where or Top clause, such as:

Select Top 10 * Into [MHS_Scribe_Test].[dbo].[KSYNC2] From [MHS_Scribe_Test].[dbo].[KSYNC]

The target table must not exist. Then use the steps above to generate the SQL script.

Another option is to create an empty database and use the Insert Into statement specifying the empty database as the target. Then, backup the database and send that to Scribe Support.

Export Example 2

This example uses an older version of SQL Server Management Studio. In SQL Server under Databases, select the appropriate database.

  • Right-click on the database and select Tasks > Generate Scripts.
  • In the Script Wizard, make sure the correct database is selected.

Exporting Data From SQL Server to Script File

Indicate whether or not the data should be included (e.g. insert statements).

Exporting Data From SQL Server to Script File

Select the appropriate objects, such as tables and views.

Exporting Data From SQL Server to Script File

Exporting Data From SQL Server to Script File

Save to a file.

Exporting Data From SQL Server to Script File