SQL Export Query

• John 19234

  SSC Enthusiast

  Points: 106

  More actions

  March 31, 2022 at 1:00 am

  #4008514

  I need to export some specific tables in database.  When using the Import/Export Wizard in SSMS, there is a screen where it gives 2 options, Copy data from one or more tables or views or Write a query to specify the data to transfer.  While I usually don't have any problems writing SQL queries for pulling data from a table or tables, I'm stumped on how to export specific tables by name.

  This database has about 200 tables and I only need to export the ones whose name ends with the word parameter. For example, tables with names like ABC_parameters, XYZ_parameters.  What am I missing here?

  Thank you

• pietlinden

  SSC Guru

  Points: 63578

  More actions

  March 31, 2022 at 1:58 am

  #4008534

  use a cursor to loop through the tables that match your criteria and pass the table to some dynamic SQL and use BCP OUT? Or use PowerShell?... maybe... <spitballing>

• Phil Parkin

  SSC Guru

  Points: 247158

  More actions

  Follow

  March 31, 2022 at 10:22 am

  #4008743

  It doesn't work the way you are hoping (ie, with some sort of table-name cursor).

  If you write a query in that window, it will export the results of running that query to the specified file.

  I have not tested this, but you may be able to use the undocumented sp_MSforeachtable to help you achieve this. Take a look here for more details.

  ---

• homebrew01

  SSC Guru

  Points: 55549

  More actions

  April 1, 2022 at 7:04 pm

  #4009477

  If it's a "One time" job, or the tables won't change, just scroll through the list of tables, clicking the ones you want.

  I don't think you can accomplish it in the query window, because that will just give you 1 destination option.

  • This reply was modified 3 years, 2 months ago by homebrew01.