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.

  • How are you trying to export this data? – McNets Feb 5 '18 at 8:14
  • @McNets I'm using Generate Scripts – Shailesh Feb 6 '18 at 4:49
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
  • Thanks for the detailed reply, it one of the first I have received so far during my stay on multiple Stackoverflow communities.<br> 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 6 '18 at 4: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 6 '18 at 9: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 8 '18 at 3: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 8 '18 at 9: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 9 '18 at 4:05
1

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.)

  • 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 8 '18 at 4:11
  • 2
    Sure, that's called a linked server query, but careful with doing large transactions across SQL Servers. – Brent Ozar Feb 8 '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 9 '18 at 4:09
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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.