March 31, 2022 at 1:00 am
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
March 31, 2022 at 1:58 am
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>
March 31, 2022 at 10:22 am
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.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
April 1, 2022 at 7:04 pm
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.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply