1

In SQL Server Management Studio 2014, How to apply a WHERE condition during Generating Script (Data only) for a particular Table?

I'm using Generate Scripts method as I dont have Remote Server access to download Database Backup. However, as the table rows are around 15k and each row is heavy, the script is not generating for the particular table.

Shailesh
  • 21
  • 1
  • 7

3 Answers3

3

You can't directly filter SSMS's commands, but here's an easy workaround:

Create another table with the subset of data you want to export:

USE TemporaryStagingDatabase;
GO
SELECT *
  INTO dbo.MyTemporaryDestinationTable
  FROM SourceDatabase.dbo.MySourceTable
  WHERE (my filtering conditions);

Ideally, you do this in a different database so you don't bloat the data & log files with your temporary export work.

Then, use SSMS's magical wizards to export the data from your staging table. (If you have to do this a lot, though, check out SSIS.)

Brent Ozar
  • 43,325
  • 51
  • 233
  • 390
1

To my knowledge, there is no WHERE clause when generating scripts (assuming you are using Right-click->Tasks->Generate Scripts).

Without knowing your use case it's hard to give you an accurate answer, but one way is to:

  1. Create a staging database that you are going to export from
  2. Select all the tables into new tables with the same names into the staging database from the original database with the WHERE clause
  3. Export that database by generating scripts.

Since you used the same table name, you only have to change the database name in the generated script (using Ctrl+H).

This is tedious since you have to define the WHERE clause for each table, but you would have to do it if Generate Scripts feature had a WHERE clause anyway. Unless all your tables have a common column you are filtering by, in which case you can use sys.tables to generate the SELECT strings.

e.g. 

SELECT 'SELECT * FROM ' + QUOTENAME(name) + ' WHERE <column> = value' 
FROM sys.tables
Heinrich Smit
  • 113
  • 9
0

with SQL server 2016, the import/export wizard, instead of "Copy" option, select the "Write query" option. it will open an editor with the ability to test the syntax or browse for a saved SQL file.

Denis Munger
  • 1