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.
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.)
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:
WHERE clauseSince 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
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.
