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

2

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
  • 42,952
  • 47
  • 220
  • 375
  • I cant create another Database on my Source Server. Is it possible to directly insert the selected rows to my table on the Destination Server. – Shailesh Feb 08 '18 at 04:11
  • 2
    Sure, that's called a linked server query, but careful with doing large transactions across SQL Servers. – Brent Ozar Feb 08 '18 at 11:54
  • Yes, since Generate Script itself is not succeeding on the Table, so I doubt whether linked server query will complete successfully.. – Shailesh Feb 09 '18 at 04:09
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
  • Thanks for the detailed reply, it one of the first I have received so far during my stay on multiple Stackoverflow communities.
    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 Feb 06 '18 at 04:51
  • So your primary objective is to have off-site backups? What's the version of SQL Server for your target server? Does it have internet access? – Heinrich Smit Feb 06 '18 at 09:47
  • Primary objective is to get backup from the database at a SQL Server 9.00.3042.00 and restore it to BigRock Windows Shared SQL Server 11.0.3156.0 – Shailesh Feb 08 '18 at 03:58
  • Won't it be possible to get the network or system admins to create a shared folder that both servers can connect to? Then you can back up to and restore from that folder. You still won't need RDP access that way, since that seems to be the biggest blocker at the moment? – Heinrich Smit Feb 08 '18 at 09:48
  • Yes, I had suggest that option already. I had even asked if they could simple put the backup file on Google drive or send ftp of the folder where the backup file can be created & downloaded via ftp. But, that is non of that is going to happen. :( – Shailesh Feb 09 '18 at 04:05
  • Sorry, I can't comment on @Brent 's answer because of my rep. Just because Generate Scripts does work, doesn't mean linked server won't work. Generate scripts usually fails because it runs out of memory with the amount of text. If you save the results to a file instead of a new query window it sometimes works. Here is a post about using linked servers for INSERT INTO operations. – Heinrich Smit Feb 09 '18 at 12:17
  • For Generating scripts for each of the tables, I have selected the option of save to file. – Shailesh Feb 09 '18 at 12:23
  • @HeinrichSmit Thanks for your contribution. You might want to consider adding the relevant comments to your answer by editing it. That would benefit the community. Comments are sort of superfluous and can be deleted. – John K. N. Oct 24 '18 at 07:04
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