• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 352
  • Last Modified:

SQL datbase export

I am trying to export a SQL database which I generally do in SQL Server Management Studio (2008) using Generate Scripts. This works fine for exporting the entire database and data, however, I'd like to just export only select fields. I tried creating a view and exporting only the view but I never saw any of the data (even after selecting  "Schema and Data" in types of data to script under Advanced.
Is there a way to export data and schema using a Select query for only certain fields and data you'd like exported?
Thanks!
0
cbeverly
Asked:
cbeverly
  • 2
1 Solution
 
nemws1Database AdministratorCommented:
Well... there's always a way.  Whether or not its a *nice* way is debatable.  There's no direct way of doing what you want.

My suggestion - create a new table containing the data structure you want and export that (then delete it):

SELECT f1
  , f2
  , f3
INTO new_table_to_export
FROM original_table
WHERE condition if you need it

// export your table ....

DROP TABLE new_table_to export

Open in new window

0
 
Rich WeisslerProfessional Troublemaker^h^h^h^h^hshooterCommented:
If you already created a view, and wanted to export the data from a view, could you not use the SQL Server Import and Export Wizard, and in the 'Select Source Tables and Views' -> select the view you wanted to export that data through?

It all just uses SSIS, so ultimately you could do even more customization by firing up your Visual Studio with BITS.
0
 
nemws1Database AdministratorCommented:
Another option would be to use 'bcp' with a query.  That'll export a table out as well, and you can specify how you want it formatted:

http://msdn.microsoft.com/en-us/library/ms191485.aspx

Not sure what you're then trying to import this into.  If its another SQL server, you can use BCP's internal binary format.
0

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now